Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Import/Export (DTS) and Replication (2000)
 FTP task using DTS

Author  Topic 

swarn
Starting Member

12 Posts

Posted - 2002-12-04 : 11:16:02
Hi every one

I have situation here which little confused me..
I have created a ftp new package (dts) for fetch database backup file from main SQL Server to another server for restrore purpose. when I execute the package then i can see the file start transfering on destinated directory but as soon as jobs end this backupfile disappear..

I tried different things such as leave overwrite check box unchecked and execute the package. In this case I have received the file but when execute again and I checked the overwrite check box then file disapppear.
I do not understand why.

another question is i couldn't find the option of if server can ftp any file to another ftp site. I mean send to rather fetch from.

third please let me know (if any done this) if can do ftp using store procedure if can then how.

thnx
swarn

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-12-04 : 11:45:18
Here's an article on using COM objects in SQL Server procedures to do FTP operations:

http://www.sqlteam.com/item.asp?ItemID=6002

Go to Top of Page

swarn
Starting Member

12 Posts

Posted - 2002-12-05 : 03:40:29
thanks robvolk for reply...

quote:

another question is i couldn't find the option of if server can ftp any file to another ftp site. I mean send ftp to another ftp site rather fetch ftp from other ftp site.


It is might silly question but i have not find in DTS any option of it. so, is this possible in SQL 2000 enterprise DTS section.

swarn

Go to Top of Page

Andraax
Aged Yak Warrior

790 Posts

Posted - 2002-12-05 : 04:52:52
Hi swarn. I have some experience of using FTP with DTS. When trying to do anything else than standard, I find that it's much better to use the Execute Process task, and script the FTP commands with a batch file and a command file. You can also execute the batch file from within a stored procedure, using the xp_cmdshell proc.



Go to Top of Page

swarn
Starting Member

12 Posts

Posted - 2002-12-05 : 07:49:43
thanx andraas

is that possible is you could share the script or guide me little more in depth of it.. I eamn batch file creation for FTP and use excecute process task method..

swarn

Go to Top of Page

VyasKN
SQL Server MVP & SQLTeam MVY

313 Posts

Posted - 2002-12-05 : 08:03:25
Yup, I didn't like the built-in FTP task as well. So, I use an Execute Process task and batch files for FTP purposes. Here's a quick example:

I have the following two lines in my batch file:

@echo off
ftp -n -v -i -s:d:\FTPStuff\instructions > FTPOutput.txt

Notice that the batch file is referencing a script file d:\FTPStucff\instructions , which containst the download instructions. Here's how the script file looks like:

open 63.32.200.26
user MyUserName MyPassword
get SomeFile1.CSV \\DownloadCenter\SomeFile1.CSV
get SomeFile2.CSV \\DownloadCenter\SomeFile2.CSV
bye

The above script file connects to the FTP site by IP addresses, specifies user name and password, and downloads to SomeFile1.CSV and SomeFile2.CSV to \\DownloadCenter and closes the FTP connection.

You could invoke this batch file using xp_cmdshell or from a DTS package, using Execute Process task.

--
HTH,
Vyas
http://vyaskn.tripod.com
Go to Top of Page

Andraax
Aged Yak Warrior

790 Posts

Posted - 2002-12-05 : 08:10:07
The batch file is what you use in the process task of DTS. It could look something like this:

------------------------------------
ftp -i -s:c:\import\runftp.txt
------------------------------------

the -i parameter tells FTP not to ask any stupid questions. The -s parameter is the path to an FTP script file, which could look something like this:

------------------------------------
open server.domain.com
username
password
cd \path
lcd c:\import
mget *.*
bye
------------------------------------

This example will get all files in the \path folder on the server to the local c:\import folder. You can issue any FTP command in the script.



Go to Top of Page

swarn
Starting Member

12 Posts

Posted - 2002-12-06 : 09:30:32
Hi guys

thanks for your's feed back. I have done that and it is like this:-

1. backup database by execute sql task and on seccess
2. run execute process task as follow
ftp transfer file (ftp_transfer.cmd)...
.........................
OPEN ftp address..
user name
password
file type (optional)
put localfile_name remote_destination
CLOSE
QUIT
.................
parameter are.......
-s:destination of batch file >ftp.log
and on completion ....
then delete the backup database file by running deletedb.bat file using again execute process task..
works fine..

but guys I have asked how can I write whole process in store procedure so the whole process will manageable from one place and hopefully quicker.. cos of newbie in sql I need help and hope you guys can...

waiting for reply with guidness and script start..

thanks again
swarn

Go to Top of Page

Andraax
Aged Yak Warrior

790 Posts

Posted - 2002-12-06 : 09:56:56
quote:


but guys I have asked how can I write whole process in store procedure so the whole process will manageable from one place and hopefully quicker.. cos of newbie in sql I need help and hope you guys can...





You can run batch files and other commands (like ren for renaming files, del for deleting files) using the xp_cmdshell extended stored procedure, or you can use the FTP functions in wininet.dll. Search SQLTeam articles for FTP if you wanna try that option.



Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-12-06 : 09:57:01
quote:
but guys I have asked how can I write whole process in store procedure so the whole process will manageable from one place and hopefully quicker
If you stitch together the parts everyone provided, you'll have exactly that.

Go to Top of Page

swarn
Starting Member

12 Posts

Posted - 2002-12-06 : 10:26:56
HI

thanks.. so you mean to say can I use dos commands in store procedure?? or am I heading to wrong direction?? please give little sample..



Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-12-06 : 10:32:42
EXECUTE master..xp_cmdshell 'ftp -n -v -i -s:d:\FTPStuff\instructions > FTPOutput.txt'

That's the FTP command from Vyas's reply. You can use xp_cmdshell to execute any command-line function (there are some limits on the type of executables you can run, see Books Online for more details)

Go to Top of Page

swarn
Starting Member

12 Posts

Posted - 2002-12-06 : 10:56:24
great........
appreciated guys.
swarn

Go to Top of Page

KnooKie
Aged Yak Warrior

623 Posts

Posted - 2002-12-08 : 07:20:09
The deleting of a single file when using FTP in SQL2000 was a bug and is fixed by applying SP1. It was only a problem when FTPing a single file. It can be fixed without applying SP1 by placing a dummy file called something like zzz.zzz at the source with the file you want to FTP and grabbing them both.

===========
Paul
Go to Top of Page
   

- Advertisement -