Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS FTP Task – Receive Files from FTP Site


SSIS FTP Task – Receive Files from FTP Site

Author
Message
Raunak Jhawar
Raunak Jhawar
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1463 Visits: 1944
Comments posted to this topic are about the item SSIS FTP Task – Receive Files from FTP Site

Regards/Raunak
Now a member of Linkedin

Please visit the all new Performance Point Forum
Please visit the all new Data Mining and Business Analytics Forum
nigel.meakins
nigel.meakins
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 76
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
Raunak Jhawar
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1463 Visits: 1944
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. :-)

Regards/Raunak
Now a member of Linkedin

Please visit the all new Performance Point Forum
Please visit the all new Data Mining and Business Analytics Forum
alpe
alpe
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 54
Nice.
but
On Receive files, and there is no files to receive
How do I catch this?
/Allan
Raunak Jhawar
Raunak Jhawar
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1463 Visits: 1944
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.

Regards/Raunak
Now a member of Linkedin

Please visit the all new Performance Point Forum
Please visit the all new Data Mining and Business Analytics Forum
charley.yu.lou
charley.yu.lou
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 228
"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
ian.miller 3310
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 117
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
NULLgarity
SSC-Addicted
SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)

Group: General Forum Members
Points: 400 Visits: 534
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.

Blog |
Twitter | LinkedIn


OldJimmy
OldJimmy
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 308
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
ian.miller 3310
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 117
I think SSIS is probably just using what's available in Windows (i.e. 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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search