Author |
Topic |
swarn
Starting Member
12 Posts |
Posted - 2002-12-04 : 11:16:02
|
Hi every oneI 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.thnxswarn |
|
robvolk
Most Valuable Yak
15732 Posts |
|
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 |
|
|
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. |
|
|
swarn
Starting Member
12 Posts |
Posted - 2002-12-05 : 07:49:43
|
thanx andraasis 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 |
|
|
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 offftp -n -v -i -s:d:\FTPStuff\instructions > FTPOutput.txtNotice 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.26user MyUserName MyPasswordget SomeFile1.CSV \\DownloadCenter\SomeFile1.CSVget SomeFile2.CSV \\DownloadCenter\SomeFile2.CSVbyeThe 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,Vyashttp://vyaskn.tripod.com |
|
|
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.comusernamepasswordcd \pathlcd c:\importmget *.*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. |
|
|
swarn
Starting Member
12 Posts |
Posted - 2002-12-06 : 09:30:32
|
Hi guysthanks 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 followftp transfer file (ftp_transfer.cmd)............................OPEN ftp address..user namepasswordfile type (optional)put localfile_name remote_destinationCLOSEQUIT.................parameter are.......-s:destination of batch file >ftp.logand 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 againswarn |
|
|
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. |
|
|
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. |
|
|
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.. |
|
|
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) |
|
|
swarn
Starting Member
12 Posts |
Posted - 2002-12-06 : 10:56:24
|
great........appreciated guys. swarn |
|
|
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 |
|
|
|