Click here to Skip to main content
15,917,059 members
Articles / Database Development / SQL Server

SFTP with SSIS Packages

Rate me:
Please Sign up or sign in to vote.
4.00/5 (9 votes)
19 Feb 2008CPOL2 min read 154.2K   5   32   8
An easy approach to access SFTP sites from SSIS packages.


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.


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
  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:
  4. 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/*
  5. Invoke the executable from SSIS (or a scheduled task in Windows) with the following syntax:
  6. "C:\Program Files\WinSCP\" -script=c:\myscript.txt


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 You can also find some documentation/samples on the WinSCP web site.

You can find this article at my blog -


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Written By
Architect Pixel Systems Inc.
United States United States
Prashant has around 8 years of experience in the IT industry. As an solutions architect (in India) he has designed/implemented couple of prestigious banking products, which now are being implented across the country. Since couple of years, he has been focussing on designing/implementing EAI/B2B applications using BizTalk Server. He is currently residing in New Jersey, USA.

Comments and Discussions

QuestionSFTP with SSIS packages Pin
ChfHess9-Mar-17 4:07
ChfHess9-Mar-17 4:07 
QuestionIf import SSIS Package will WinScp code copied Pin
GPEnu11-Oct-12 9:54
GPEnu11-Oct-12 9:54 
AnswerExcellent Pin
JerseyNo1021-Nov-11 5:20
JerseyNo1021-Nov-11 5:20 
GeneralMy vote of 5 Pin
RussBell31-Jan-11 9:35
RussBell31-Jan-11 9:35 
GeneralGuide to using WinSCP as task in SSIS Pin
prikryl1-Apr-09 21:22
prikryl1-Apr-09 21:22 
QuestionConnecting WinSCP with SSIS Pin
avikerchithi24-Oct-08 6:36
avikerchithi24-Oct-08 6:36 
AnswerRe: Connecting WinSCP with SSIS Pin
Eugene Mayeski19-Dec-08 21:20
professionalEugene Mayeski19-Dec-08 21:20 
AnswerRe: Connecting WinSCP with SSIS Pin
#realJSOP17-Jun-18 6:08
professional#realJSOP17-Jun-18 6:08 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.