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 3rd Party software, which might be expensive. If you have simpler needs then this article is for you. Its 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 SFTP site rather than the regular FTP. The problem starts here, SSIS does not support SFTP protocol by default. I did made a lot of search on internet and all my friends. What I could figure out was that the only method is to use third party extensions (which are definitely pricey). My requirment was very simple, only need 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 though my experience would be useful to someone else.

Using the Code

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

What you need to do

1. Installed WinSCP client utility from http://winscp.net/eng/download.php
2. 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 & money, then you can go with this workaround. If you do face any issues please email me at prashant.baker@yahoo.com. You can also find some documentation/samples on winscp web site.

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