SSIS FTP Task – Receive Files from FTP Site

  • Comments posted to this topic are about the item SSIS FTP Task – Receive Files from FTP Site

    Raunak J

  • Good article on use of the FTP task. However, given that just about any client who values their data would use SFTP and not FTP, the task has very limited usage scenarios (even pushing data around internally without SFTP makes some clients twitchy). We have tended to create a script task and reference open source libraries for SFTP (ensuring we have all the source code of course before thinking about using in production). Haven't seen whether the task in SQL2012 can support this as yet.

  • Thanks for the feedback.

    The FTP Task does not supports SFTP. Infact, there is no native support to SFTP as yet. Hence, a third party task or a custom code as you have mentioned. 🙂

    Raunak J

  • Nice.

    but

    On Receive files, and there is no files to receive

    How do I catch this?

    /Allan

  • The usual practice what I have observed is "crude". The FTP initiates at certain time during the day, which is believed that the FTP host server will have the files. Hence, no issue. If there is a delay, the package has be run manually.

    Alternately, one can come up with better modular designs such as, emailing or updating a certain flag when the files or the load is made available on the FTP server. The file transfer can then be triggered after verifying the flag status.

    Raunak J

  • "SSIS has a built-in feature for FTP, but point to be noted here is that Integration Services has native support for only FTP and not SFTP. "

    Yes, this is unfortunately. Many customers are preferred SFTP or FTPS protocols than just FTP for security reason. So, SSIS FTP task will have a quite limited usage. Since the SFTP and FTPS are more and more porpular, Microsoft should enhance their FTP task to support it.

    Currently, I have to use Execute Process task with WinSCP command online to perform the FTPS or SFTP file transfer in SSIS package.

    Thanks,

    Charley

  • Hi - I too need to chime in on the use of ftp. I think if I had the choice between ftp and snail mail and would always choose the latter. Microsoft really needs to join the 90's and license any one of the plethora of very reliable sftp clients out there.

  • I agree with what the others have chimed in with. Without support for SFTP, the FTP task is worthless in my opinion. I typically use Execute Process task to kick off a third-party client that supports SFTP.

  • One more vote for the Execute Process Task using a third-party FTP client that is full-featured. In today's world, it is uncommon to find vanilla FTP with password support. FTP is still a viable protocol but support for security measures is essential. Hopefully future versions of SSIS will provide a more robust FTP component.

  • I think SSIS is probably just using what's available in Windows (i.e. http://ftp.exe) and what we really need is for Microsoft to have a command line sftp.exe and ssh.exe builtin to Windows that SSIS could then leverage off of.

  • I hope people aren't rating the article down because it uses FTP and not SFTP. That's what SSIS provides, and we wanted to give a tutorial on how to use it.

    This was a good job, Raunak, of explaining how to do this.

  • Another hole in the SSIS FTP task is that it does not work with mainframes. I'm doing internal file transfers so don't need SFTP. But do need to be able to access FTP files that don't begin with a "\" (or is it "/") root.

    Same solution as others have mentioned, use a command line interface. Actually, to simplify things (?) I'm moving this out of SSIS and into the SQL Server Agent job that invokes the package.

Viewing 12 posts - 1 through 12 (of 12 total)

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