xp_cmdshell copy from ftp site

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

The OS command interpreter's COPY command do not know what an FTP site is.

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

And do not schedule to run the ftp exe via xp_cmdshell.

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

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

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

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

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

Good point, Linchi....

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

Bob,I need to upload files to a FTP server, and I've just created a

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

Thanks for the reply and advice Henrik.

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

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

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

Hi Bob,I'm glad it worked.Best regards,Henrik Staun Poulsenwww.stovi.

Hi Bob,

I am glad it worked.

Best regards,
Henrik Staun Poulsen
www.stovi.com


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

Plz helpppp

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