SFTP with SSIS Packages

Introduction

By default, SQL Server Integration Services (SSIS) do not support access to SFTP sites. One way of getting it done is by using third party software, which might be expensive. If you have simpler needs, then this article is for you. It's a kind of workaround, but it works.

Background

I had this situation in my project where I should update my SSIS package to retrieve files from an SFTP site rather than the regular FTP. The problem starts here, SSIS does not support the SFTP protocol by default. I did a lot of search on the internet and with all my friends. What I could figure out was that the only method is to use third party extensions (which are definitely pricey). My requirement was very simple, and only needed to download files, for which it was not feasible to purchase a third party software. After lots of research, I would do it with a work-around. I thought my experience would be useful to someone else.

Using the Code

There's a free tool called WinSCP which provides a command line utility to communicate with SFTP sites. You just need to understand the scripting language for the command line and execute the executable from SSIS.

What you need to do

1. Install the WinSCP client utility from http://winscp.net/eng/download.php
2. The command line utility is located at C:\Program Files\WinSCP\WinSCP.Com.
3. Create a script file (say myscript.txt). The following sample might give you some idea:

option batch on
option confirm off
open sftp://user:password@ipaddress:port
cd mydir
option transfer binary
get *.dat c:\mylocaldir
# Move the contents on remove folder
mv *.dat ./backup/*
close
exit

4. Invoke the WinSCP.com executable from SSIS (or a scheduled task in Windows) with the following syntax:

"C:\Program Files\WinSCP\WinSCP.com" -script=c:\myscript.txt

Summary

If you have a problem statement wherein you need to perform simpler tasks from SFTP web sites viz. retrieving or uploading files, and do not wish to spend much time and money, then you can go with this workaround. If you face any issues, please email me at prashant.baker@yahoo.com. You can also find some documentation/samples on the WinSCP web site.

You can find this article at my blog - http://prashantbaker.blogspot.com/search/label/SSIS.