Post reply

SSIS FTP Task

  • 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

  • December 19, 2007 at 9:14 am

    #761443

    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://qa.sqlservercentral.com/Forums/Topic434011-148-1.aspx#bm434214

    Good luck 🙂

    Tommy

    Follow @sqlscribe

  • December 19, 2007 at 12:08 pm

    #761509

    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/

  • December 20, 2007 at 9:49 pm

    #761973

    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

  • December 21, 2007 at 7:15 am

    #762100

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

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

    Tommy

    Follow @sqlscribe

  • April 26, 2011 at 3:30 pm

    #1316267

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

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

    www.sql-library.com[/url]

  • August 9, 2012 at 3:25 pm

    #1524133

    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

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

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