How to Use SFTP within SSIS

Introduction

Recently, I had the need to download a file from a vendor of ours and import the data into SQL Server. We already had an SSIS (SQL Server Integration Services) package in production that would read the file and import the data into our SQL table. I needed to add onto our SSIS package a task to connect to the vendor’s FTP site and download the file.

SSIS has a built in FTP task, but this only works for the FTP protocol. I needed to use SFTP. After much research and building a solution by piecing together bits from many sources, I decided to write an article that gives a start to finish overview of how to download a file using SFTP inside SSIS. I am also trying to document the things I found out the hard way. Your needs may be different, but hopefully this helps someone get past some of the hurdles I stumbled over.

Background

There are a number of different tools to use SFTP from SSIS. You could download a free, or purchased command line client such as WinSCP, PuTTY, etc. You could also purchase an add-on SSIS components such as Pragmatic Works Task Factory.

I decided to use the free winSCP command line tool. It has just enough functionality and it was free! To use winSCP from SSIS, the only way I found that worked well, was to use their .NET assembly within a script task in SSIS.

First things first, there are a number of things to get setup.

Install winSCP

1. Download and install winSCP on your development machine and production server where your SSIS package will ultimately run. By default, it will install at C:\Program Files (x86)\WinSCP
   http://winscp.net/eng/download.php
2. Download and install the winSCP .NET assembly?/?COM library. This download is just a zip file with two files inside, a DLL and a readme. I copied those files to the same location where winSCP was installed C:\Program Files (x86)\WinSCP
   • http://winscp.net/eng/download.php
   • http://winscp.net/eng/docs/library_install
3. Install the winSCP assembly into the Global Assembly Cache (GAC). In your computer's Admin Tools, use the Microsoft .NET Framework 2.0 Configuration to add the winSCP.dll to the GAC
4. NOTE: For me, I had an old version of winSCP on our production server that I needed to upgrade to the latest version before the .NET assembly would work. I received an error when running the SSIS package that the version of the winSCP.exe did not match the version of the .NET assembly. This warning can be disabled through code, but it is best to have the correct matching versions. Here is the error message from the winSCP DLL:

Error when using WinSCP to upload files: WinSCP.SessionLocalException: The version of C:\Program Files (x86)\WinSCP\WinSCP.exe (4.2.9.0) does not match version of this assembly C:\WINDOWS\assembly\GAC_MSIL\WinSCP\1.0.7.3446__b5f19f5762436b89\WinSCP.dll (5.1.7.0). You can disable this check using Session.DisableVersionCheck (not recommended).

Now, find the sshKey for the SFTP site you are connecting to.

As you will see in the code below, the FTP session needs to know the ssh host key. Now, the winSCP FAQ (http://winscp.net/eng/docs/faq_script_hostkey) said to either get it from the server administrator (not possible in my case) or to do some other complicated process.

Here is what worked for me:

1. Run winSCP.exe, (it should be here: C:\Program Files (x86)\WinSCP)
2. Log into the FTP site
3. Under the Command menu, choose “Server and protocol information”
4. Copy the text “server host key fingerprint”
   It should be all text and look like this ssh-rsa 2048 ab:12:ab:cd:12:34:56:78:ab:cd:ef:gh:12:23:34:a2
   Keep this for the code below.

Setup your SSIS package

1. Inside your SSIS package, drag on a new script task.
2. Create variables to hold all the information needed by the code. You don’t have to do this, you can hardcode these values in the code, but for maintenance it is easier to have them in variables. Here are my SSIS package variables with example values:
   1. winSCPPath = "C:\Program Files (x86)\WinSCP\winscp.exe"
   2. ftpUsername = "{username}"
   3. ftpPassword = "{password}"
   4. ftpSite = "{ftp.sitename.com}"
   5. localPath = "{path to the local folder to download the file to"
   6. remoteFTPDirectory = "/home/folderWhereTheFileLives"
   7. sshKey = "ssh-rsa 2048 ab:12:ab:cd:12:34:56:78:ab:cd:ef:gh:12:23:34:a2";
      See above for instructions on how to find this value.
   8. winSCPLog = true/false
   9. winSCPLogPath = "{path to local folder to save the log file to for debugging if necessary}"

Using the Code

Here is the code to go in your script task. Make sure to add this namespace:

using WinSCP;

string winscpPath = Dts.Variables["winSCPPath"].Value.ToString(); 
string username = Dts.Variables["ftpUsername"].Value.ToString(); 
string password = Dts.Variables["ftpPassword"].Value.ToString(); 
string ftpSite = Dts.Variables["ftpSite"].Value.ToString(); 
string localPath = Dts.Variables["localPath"].Value.ToString(); 
string remoteFTPDirectory = Dts.Variables["remoteFTPDirectory "].Value.ToString(); 
string sshKey = Dts.Variables["sshKey"].Value.ToString();
Boolean winSCPLog = (Boolean)Dts.Variables["winSCPLog"].Value;
string winSCPLogPath = Dts.Variables["winSCPLogPath"].Value.ToString();
            
SessionOptions sessionOptions = new SessionOptions
{
Protocol = Protocol.Sftp,
HostName = ftpSite,
UserName = username,
Password = password,
SshHostKeyFingerprint = sshKey
};

try
{
  using (Session session = new Session())
  {
    // WinSCP .NET assembly must be in GAC to be used with SSIS,
    // set path to WinSCP.exe explicitly, if using non-default path.
    session.ExecutablePath = winscpPath;
    session.DisableVersionCheck = true;

    if(winSCPLog)
    {
      session.SessionLogPath = @winSCPLogPath + @"WinscpSessionLog.txt";
      session.DebugLogPath = @winSCPLogPath + @"WinscpDebugLog.txt";
    }

    // Connect
    session.Timeout = new TimeSpan(0,2,0); // two minutes
    session.Open(sessionOptions);

    TransferOptions transferOptions = new TransferOptions();
    transferOptions.TransferMode = TransferMode.Binary;

    try
    {
      session.GetFiles(remoteFTPDirectory + "/" + 
      fileToDownload, localPath, false, transferOptions);
    }
    catch (Exception e)
    {
      Dts.Events.FireError(0, null,
      string.Format("Error when using WinSCP to download file: {0}", e), null, 0);
      Dts.TaskResult = (int)DTSExecResult.Failure;
    }
  }
}
Dts.TaskResult = (int)ScriptResults.Success;

History

• 31^st October, 2013: Initial version