SQL Server - xp_cmdshell copy from ftp site

Asked By Bob McClellan

04-Aug-09 11:10 AM

I am trying to automate a daily process.
I currently download an .xml file each day then
run a sp that shreds and processes it into one of our DBs.

I'd like to create a job to do this.
my thought was to use xp_cmdshell to bring down the file
but my EXEC statement errors out.

EXEC xp_cmdshell 'copy ftp://pathToFtpSite/Tuesday_08042009.xml
\\MyServerIp\Data\Downloads'
GO

the output returns
The syntax of the command is incorrect.

Any suggestions on how to automate this process is much appreciated.
thanks in advance,
..bob

04-Aug-09 11:36 AM

The OS command interpreter's COPY command do not know what an FTP site
is. Use some command-line FTP utility (should be plenty if you Google)
or perhaps the built-in FTP.EXE.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

04-Aug-09 12:18 PM

And do not schedule to run the ftp exe via xp_cmdshell. Just add a step to
exxecute it as a straight OS job step.

Linchi

04-Aug-09 01:42 PM

Thanks Tibor.
I will look for a comman line utility as you suggest.

04-Aug-09 01:44 PM

Thanks Linchi.
I will set it up as it is own OS job step.

04-Aug-09 01:56 PM

Good point, Linchi.
... or perhaps use an SSIS package which has FTP built-in as a task.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

06-Aug-09 12:42 AM

Bob,

I need to upload files to a FTP server, and I have just created a small
upload.bat file, that I get xp_cmdshell to run, after it has created
the files.

upload.bat looks like this:

ftp -s:upload.dat ftp.myOwnSite.com

upload.dat looks like this:

userid
password
binary
put myfile.txt
quit

Line 1 is the UserID for the FTP site, Line 2 is the password for this
site. Then I use the Binary FTP command, to ensure that it is treated
correctly. Then I upload (put) the file I want and finally I tell it
to quit processing.

FTP.com is a part of Windows, and is installed on all PCs.

Best regards,
Henrik Staun Poulsen
www.stovi.com

05-Aug-09 07:28 PM

Thanks for the reply and advice Henrik.

This looks like it should do exactly what I need.
I will play around with this.  I just need to be able to tell it
dynamically each day what file to pull down.  It is always a filename with
Account# + dayOfWeek + CurrentDaysDate.
My SP easily strings this together.  I am sure I can get the .bat file
to do the same.

Thanks again,
..bob

Bob,

I need to upload files to a FTP server, and I have just created a small
upload.bat file, that I get xp_cmdshell to run, after it has created
the files.

upload.bat looks like this:

ftp -s:upload.dat ftp.myOwnSite.com

upload.dat looks like this:

userid
password
binary
put myfile.txt
quit

Line 1 is the UserID for the FTP site, Line 2 is the password for this
site. Then I use the Binary FTP command, to ensure that it is treated
correctly. Then I upload (put) the file I want and finally I tell it
to quit processing.

FTP.com is a part of Windows, and is installed on all PCs.

Best regards,
Henrik Staun Poulsen
www.stovi.com

07-Aug-09 04:41 PM

Henrik,
I got the batch file working.  Thanks again for this advice,
..bob

Bob,

I need to upload files to a FTP server, and I have just created a small
upload.bat file, that I get xp_cmdshell to run, after it has created
the files.

upload.bat looks like this:

ftp -s:upload.dat ftp.myOwnSite.com

upload.dat looks like this:

userid
password
binary
put myfile.txt
quit

Line 1 is the UserID for the FTP site, Line 2 is the password for this
site. Then I use the Binary FTP command, to ensure that it is treated
correctly. Then I upload (put) the file I want and finally I tell it
to quit processing.

FTP.com is a part of Windows, and is installed on all PCs.

Best regards,
Henrik Staun Poulsen
www.stovi.com

11-Aug-09 06:13 AM

Hi Bob,

I am glad it worked.

Best regards,
Henrik Staun Poulsen
www.stovi.com


d-44d7-88f6-1f0f1dc67609@k1g2000yqf.googlegroups.com...

21-Jul-10 05:02 PM

Bob,



I tried out your code. I am trying to put a file in my PC to a FTP site. The code creates the file in the FTP with the same name and format but has no content. Please help me.



Thanks in advance.



Regards,

Tom