SSIS : Secure FTP and SSIS?

Downloading files from a FTP server is a common task and once in a while you need to configure this in SSIS. In SSIS you have a FTP task. Neat! No problem you think. Well there is a problem when you have a Secure FTP server. I'll explain this in the post below.

When you drag an FTP task from the toolbox in to the package and press create new connection you'll get the following screen:

But this works only for unsecure FTP. When i tried to connect to the FTP server with FileZilla i noticed that there was an 's' before the 'ftp'. I didn't entered this myself but it appeared after i entered the servername.

After some googling i found out that the FTP task of SSIS doesn't support secure FTP. I checked Denali and the windows seems the same. So no improvements yet! I've seen the software SSIS utilities app but the price is way outside the range for this. I have found however the open source WinSCP tool that has a command line interface that can be used to complete this : http://winscp.net/eng/docs/scripting. I decided not to use the FTP task but to use the WinSCP tool together with 'Execute process task'. So in stalled WINSCP and tested the commandline option:


winscp.com /command "option batch on" "option confirm off" "option transfer ascii" "open <username>:<password>@<server>" "get * E:\SSIS\<project>\ImportFiles\*" "close" "exit"
and this works. So this is Execute Proces Task window.

Conclusion : I also had some troubles with the webservice task (due to limited functionality) and it seems that this component is also very limited and therefore you need 3rd party software which i normally don't prefer.

Greetz,
Hennie