How to access SFTP with SSIS using SSH.NET

How to access SFTP with SSIS using SSH.NET. I have been looking around for an all encompassing guide to setting up and connecting to an SFTP server with SSIS and have been unable to find such an article. After some trial and error, I have put together how I was able to accomplish accessing an SFTP server with SSIS using SSH.NET by Renci.

Configure SSIS

⦁ Make new SSIS Project
⦁ Next, add script tag to control flow
⦁ Then, edit script
⦁ Right click References and select ‘Manage NuGet Packages’

⦁ After that, go to Browse and find the following to packages and versions
⦁ SSH.NET by Renci – 2016.0.0
⦁ SshNet.Security.Cryptography by Renci – 1.2.0

⦁ Right click on the project and select properties – I am using 4.5

⦁ Add the following name space to the script
⦁ using Renci.SshNet;
⦁ Add the following code to connect to the server and print out the files in the desired directory

Code for SFTP connection with SSH.NET

public void Main()
{
  // SFTP credentials – smart to make these SSIS variables and read them in
  var host = “host.com”; //No “sftp://” prior to host
  var port = 2222;
  var username = “user”;
  var password = “pass”;

  //Directories
  var remoteDir = “/”; //This is the desired remote directory

  //Create client with credientials
  using (var client = new SftpClient(host,port,username,password))
  {
  Console.WriteLine(“Connect to ” + host + ” as ” + username);
  client.Connect();
  Console.WriteLine(“Connected.”);

  //List files in target server directory
  var files = client.ListDirectory(remoteDir);

  Console.WriteLine(“Files in ” + remoteDir + “: “);

   foreach (var file in files)
  { 
    if (client.Exists(remoteDir + file.Name)) //Not needed
    {
       Console.WriteLine(“Exists: ” + file.Name);
     }
  }
}

Dts.TaskResult = (int)ScriptResults.Success;
}

Great,  now we have all the base SSIS and C# looking good let’s try to run it!
Oh no! We get an error in the script!

Error: Could not load file or assembly ‘Renci.SshNet, Version=2016.1.0.0, Culture=neutral, PublicKeyToken=1cee9f8bde3db106’ or one of its dependencies. The system cannot find
the file specified.

Install packages to GAC manually

For some reason, SSIS does not automatically see the packages you installed. We need to manually download the dll’s now for our referenced packages and install them to the GAC (Global assembly cache). Go to the following links and download the packages.

https://www.nuget.org/api/v2/package/SSH.NET/2016.0.0
https://www.nuget.org/packages/SshNet.Security.Cryptography/1.2.0

Once they are downloaded, we can unzip the packages with 7-Zip. In the Downloaded package, ssh.net.2016.0.0 > lib > netstandard1.3, I can see the dll that I want to manually add to the GAC.

Open ‘Developer Command Prompt for VS 2017’ – and Run as Admin

After that, run the following command for both packages
gacutil /i [PATH TO DLL] /f

Now when we run our script we can see that the packages get recognized! In conclusion, we need to install the packages with NuGet, write our code to connect, then manually install the same versions of the dll’s so SSIS can see the packages we wish to use.

Now that we know how to access SFTP with SSIS using SSH.NET, we can write our script to pull files, check if files exist, delete files, push files, and more.

For more SSIS development blogs, read on!

For more tech blogs, subscribe to the code_marks news letter: http://eepurl.com/gZCMQz