SSIS FTP Task – Receive Files from FTP Site

Question

SSIS FTP Task – Receive Files from FTP Site

Raunak Jhawar

SSCoach

Points: 15701
More actions
May 28, 2012 at 9:51 pm

#262120

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

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

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

nigel.meakins SSC Veteran Points: 220 More actions · Answer 1

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.

Raunak Jhawar SSCoach Points: 15701 More actions · Answer 2

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

alpe SSC Enthusiast Points: 119 More actions · Answer 3

Nice.

but

On Receive files, and there is no files to receive

How do I catch this?

/Allan

Raunak Jhawar SSCoach Points: 15701 More actions · Answer 4

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

charley.yu.lou SSC Rookie Points: 42 More actions · Answer 5

"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

ian.miller 3310 SSC Rookie Points: 48 More actions · Answer 6

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.

NULLgarity SSCommitted Points: 1602 More actions · Answer 7

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.

Twitter | LinkedIn

OldJimmy SSC Veteran Points: 233 More actions · Answer 8

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.

ian.miller 3310 SSC Rookie Points: 48 More actions · Answer 9

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.

Steve Jones - SSC Editor SSC Guru Points: 736455 More actions · Answer 10

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.

george-975422 Valued Member Points: 68 More actions · Answer 11

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.