WinSCP sFTP Tasks for my SSIS Package Template

I have an SSIS Template package to speed up development. I use WinSCP as an sFTP task as the standard SSIS FTP Task can't do sFTP and all my recent development requirements have specifically asked for sFTP. There are many tools you can use. I like WinSCP because it works well both as an application and from the command line, it's got good online documentation for scripting command line tasks and it's free.

I call WinSCP from an Execute Package Task. This requires an installation on the server where the SSIS package executes - I don't run ETL tasks on a production server but I've found many instances where legacy systems do. I prefer to use a powerful workhorse BI/ETL server where I can guarantee an extensive overnight maintenance window and keep the production environment both secure and dedicated to its primary role.

In my SSIS Template I have a preconfigured PUT and GET task both driven from variables populated from a configuration table in my Metadata database. I could, if required, encrypt the sFTP credentials within the configuration table and then decrypt within the stored procedure that creates the string passed to the variable. But let's not over complicate matters unless we have to.

I build the sFTP command line arguments from variables which are collected from my configuration control table. This table can be amended - if for example an FTP password requires regular changes - without having to change the SSIS package.I feel this is a better solution than using a text file script as the text files then need to be version controlled, secured and backed up. Using a configuration table and SSIS variables allows other benefits such as security, centralisation of control data and the potential to encrypt if required.

I configure the Execute Process Task as shown here.

Below are examples of my SSIS expression and the output it returns.

Get File(s)

Expression - @[User::FTPPrefix] +  @[User::FTPUsername] + ":" +  @[User::FTPPassword] + "@" +  @[User::FTPHost] + "\" \"get " + "\" \"//*.csv \" \"" +  @[User::FTPDestinationFolder] +  @[User::FTPSuffix]

Output - /console /command "option batch on" "option confirm off"  "option transfer binary" "open sftp://username:password@mydomain.ftp.com" "get " "//*.csv " "C:\Temp "close" "exit"

Put File(s)

Expression - @[User::FTPPrefix] +  @[User::FTPUsername] + ":" +  @[User::FTPPassword] + "@" +  @[User::FTPHost] + "\" \"put \"" +  @[User::FTPSourceFolder] +  "InsertFileNameHere" + (DT_STR,4,1252)YEAR(GETDATE()) + RIGHT("0" + (DT_STR,2,1252)MONTH(GETDATE()),2) + RIGHT("0" + (DT_STR,2,1252)DAY(GETDATE()),2) + ".zip" + "\" \"//\"\"" +  @[User::FTPSuffix]

Output - /console /command "option batch on" "option confirm off"  "option transfer binary" "open sftp://username:password@mydomain.ftp.com " "put "\\172.0.0.1\C$\sFTP\InsertFileNameHere20120301.zip" "//"" "close" "exit"

I hope you find this useful. I'll be sharing my full SSIS template shortly. 

UPDATE: Check out my 7-Zip archiving task also used as part of my SSIS package template.