Working with SFTP sites in SSIS

The year is almost half over and I haven’t fished at all yet. I did no ice fishing this year and I haven’t gotten out in the first few weeks of the open water season either. Life has been busy, with a small increase in work responsiblities along with a large increase in family busyness. I hope to get out soon, and if so I will provide a fishing report. The spring was cold, so I expect a few weeks to pass before fishing really gets going this year anyway.

I often use SSIS for collection of files. My current employer pulls data from several different locations, and since my background is much more SQL based than programming based, I default to SSIS. One thing to note when working in SSIS is that while Integration Services has an ftp task, it doesn’t support pulling files from an sftp site. This can be confusing when you first try to use the ftp task to pull data from an sftp site. There is no explicit warning that you are using an incorrect component. You can adjust the port to the standard sftp port, but the connection to the site fails.

To get around this limitation, I use the scripting capabilities of WinSCP. WinSCP is a great tool – I love the UI for interacting with the ftp/sftp sites, the scripting language is intuitive and easy to use, and the options for more secure protocols have always met my needs.

To demonstrate how it works, I will show a package I set up to extract data and put it on an sftp site. The package has three components. The first is a data flow task which extracts the data from SQL Server and places it on the file system as a flat file. The second task is an Execute Process task which kicks off a batch file that runs the WinSCP script to transfer the file. The third task archives the exported file to an Archive folder. The process is set up to run daily, so each file has the date appended to its name.
20180515 SSIS Package
The batch file is quite simple and just kicks off the WinSCP scripting program with the pre-written script. Here is an example of such a batch file:

cd "Program Files (x86)\WinSCP" -script="C:\Project\3rd Party Vendor Unsubscribe Feed\lib\winscp ftp script.txt"

Finally, the WinSCP script is fairly simple too. First make sure to turn off any interactive prompts, since this will be run in an automated fashion. Then connect to the sftp site with the “open” command and place the file out there with the “put” command. (Similarly, to pull files you can use the “get” command). The WinSCP site has many great examples for writing scripts to work with ftp/sftp files and folders. The WinSCP scripting language is even robust enough to allow for variables, such as the %TIMESTAMP#yyyymmdd% one you can see in my script below.

# Automatically abort script on errors
option batch abort
# Disable overwrite confirmations that conflict with the previous
option confirm off
open s -hostkey="ssh-rsa 2048 XX:XX:XX:XX:XX:XX:XX:XX:XX:XX:XX:XX:XX:XX:XX:XX"
put "C:\Project\3rd Party Vendor Unsubscribe Feed\Files\UnsubscribeEmails_%TIMESTAMP#yyyymmdd%.txt" "/subscriptions/unsub/"

Using this simple technique you can still use SSIS to transfer files to and from an sftp site.