Click here to Skip to main content
Click here to Skip to main content

SFTP with SSIS Packages

, 19 Feb 2008
Rate this:
Please Sign up or sign in to vote.
An easy approach to access SFTP sites from 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:
  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/*
    close
    exit
  5. Invoke the WinSCP.com executable from SSIS (or a scheduled task in Windows) with the following syntax:
  6. "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.

License

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

About the Author

Clement Prashant Baker
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

 
QuestionIf import SSIS Package will WinScp code copied PinmemberGPEnu11-Oct-12 9:54 
If import SSIS Package will WinScp code copied or do we need to install WinScp on the SSIS server where we are running package
AnswerExcellent PinmemberJerseyNo1021-Nov-11 5:20 
GeneralMy vote of 5 PinmemberRussBell31-Jan-11 9:35 
GeneralGuide to using WinSCP as task in SSIS Pinmemberprikryl1-Apr-09 21:22 
QuestionConnecting WinSCP with SSIS Pinmemberavik1024-Oct-08 6:36 
AnswerRe: Connecting WinSCP with SSIS PinmemberEugene Mayeski19-Dec-08 21:20 

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

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

| Advertise | Privacy | Mobile
Web01 | 2.8.140718.1 | Last Updated 19 Feb 2008
Article Copyright 2008 by Clement Prashant Baker
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid