SSIS FTP Task

Question

SSIS FTP Task

devic1981

SSC Enthusiast

Points: 193
More actions
December 19, 2007 at 8:32 am

#377568

Hi,
I have to copy 3 files daily from an ftp server.The files will have the name abcyyyymmdd.csv.Everyday I have to query a table to check the last processed file and then copy the next day's file from FTp server.
For example if I have abc20071219 in the table i hav e to copy the file abc20071220.csv.
Can anyone of you help me in doing this.
Can i acheive this functionality using the fTP script task.
I am newbie to SQL server ...
Thanks,
Devi

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Tommy Bollhofer SSChampion Points: 14940 More actions · Answer 1

You could use a script task or build an FTP command file with dynamic SQL and use an execute process task. Refer to some of the examples in this post:

http://www.sqlservercentral.com/Forums/Topic434011-148-1.aspx#bm434214

Good luck 🙂

Tommy

Follow @sqlscribe

Norman Kelm SSCommitted Points: 1922 More actions · Answer 2

How about changing the query to return the next file name using the T-SQL date functions. Store the result in a SSIS package variable and set the FTP Task up to use the remote path from the variable.

Norman

DTS Package Search

http://www.dtspackagesearch.com/

devic1981 SSC Enthusiast Points: 193 More actions · Answer 3

Great..Thanks for the input

..I did like that and now I have a problem..When i execute the ftpscript task I have an error saying that the file

[FTP Task] Error: Variable "strEmpFile" doesn't start with "/".

The variable strEmpFile is being used as the Remote path variable.

Thanks,

Devi

Tommy Bollhofer SSChampion Points: 14940 More actions · Answer 4

Try something like (assuming the remote path on the FTP server is /)

"/" + DTS.Variables("strEmpFile").Value.ToString

Tommy

Follow @sqlscribe

Jules Bonnot SSCertifiable Points: 5488 More actions · Answer 5

using CLR procedure, much more secure,robust free and extensible solution here

http://sql-library.com/?p=186

www.sql-library.com[/url]

donna_123_a SSC Enthusiast Points: 172 More actions · Answer 6

I got this error recently from the FTP task in SSIS:

Unable to send files using "FTP".

I had everything correct, by the book. I was trying to send a file, but I got the same error when trying to receive a file. I already verified that I could send the file via command line FTP, so the FTP was not "secure FTP" (which wouldda required FileZilla or WinSCP or something similar) and my credentials were fine.

But I still got the above error. I was beginning to thing that the FTP task in SQL Server 2008 was broken and just couldn't send files.

Well, this issue is now resolved. The problem was in the destination folder. I was assuming that the root folder for my FTP account held the files. So I entered the destination folder as this: /data_in/

But... the FTP task sees the root folder as one up from that. So I had to use my account name in the path... meaning I had to change the destination folder to this: /myaccountname/data_in/

(where "myaccountname" was my login to the FTP site.)

I hope this post helps someone else.

Donna