How to access SFTP with SSIS using SSH.NET

Tutorial to download from SFTP in Integration Services

SSIS / SFTP / SSH.NET / Renci / Integration Services / PowerShell / Windows Server

Introduction

For the ones unware of SFTP (SSH File Transfer Protocol), it is a secure channel with SSH2 encryption established between two machines. You can think of it as FTP with encryption and a few more differences. For more information about this you can read this blog post.

There are some alternatives to get this done, ones paid and others free. I will focus on the use of the excellent SSH.NET because I couldn’t find a good guide to get it to work with SSIS. It’s worth mencioning WinSCP as well, and the excellent guide to make it work with Integration Services.

The GAC

The recommended way of installing SSH.NET is by using Nuget, but the only way to SSIS to be able to access the library is installing the library to the GAC.

  1. Download the file from the Nuget site
  2. Unzip the downloaded file
  3. Install to the GAC with the above PowerShell script as an administrator:
1
2
3

[System.Reflection.Assembly]::Load("System.EnterpriseServices, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a")
$publish = New-Object System.EnterpriseServices.Internal.Publish
$publish.GacInstall("Renci.SshNet.dll")

Create the project

You can see two things in my code that are worthy of note. The first one was the file I had to download and the second the problems with the way security was implemented in the server. For the first one I created the selectedZip variable that contains the name of the correct file to download. The second forced me to create an event to simulate the keyboard typing the password on the server. You can see that on the code below that you can paste to a Script Task. Don’t forget to add the following DLL references:

  • System.IO.Compression
  • System.IO.Compression.Filesystem
  • Renci.Ssh.Net
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64

using Renci.SshNet;
using Renci.SshNet.Common;
using Renci.SshNet.Sftp;
using System;
using System.IO;
using System.IO.Compression;
using System.Linq;
using ConnectionInfo = Renci.SshNet.ConnectionInfo;

public void Main()
        {
            string host = Dts.Variables["$Project::sftpUrl"].Value.ToString();
            string username = Dts.Variables["$Project::sftpUser"].Value.ToString();
            string password = Dts.Variables["$Project::sftpPassword"].Value.ToString();
            string remoteDirectory = Dts.Variables["$Project::sftpRemotePath"].Value.ToString();
            string localDirectory = Dts.Variables["$Project::sftpLocalPath"].Value.ToString();

            KeyboardInteractiveAuthenticationMethod kauth = new KeyboardInteractiveAuthenticationMethod(username);
            PasswordAuthenticationMethod pauth = new PasswordAuthenticationMethod(username, password);

            //Event to simulate keyboard password entry
            kauth.AuthenticationPrompt += new EventHandler<AuthenticationPromptEventArgs>(HandleKeyEvent);

            ConnectionInfo connectionInfo = new ConnectionInfo(host, 22, username, pauth, kauth);

            using (var sftp = new SftpClient(connectionInfo))
            {
                sftp.Connect();
                var files = sftp.ListDirectory(remoteDirectory);

                SftpFile selectedZip;

                //Selecting the most recent file
                selectedZip = files.Where(x => x.Name.Contains(".zip")).OrderByDescending(x => x.Name).FirstOrDefault();

                string localZip = string.Empty;

                using (var localFile = File.Open(localDirectory + selectedZip.Name, FileMode.Create))
                {
                    sftp.DownloadFile(selectedZip.FullName, localFile);
                    localZip = localFile.Name;
                }

                using (ZipArchive archive = ZipFile.OpenRead(localZip))
                {
                    foreach (ZipArchiveEntry entry in archive.Entries)
                    {
                        entry.ExtractToFile(Path.Combine(localDirectory, entry.FullName), true);
                    }
                }
            }
            Dts.TaskResult = (int)ScriptResults.Success;
        }

        void HandleKeyEvent(Object sender, Renci.SshNet.Common.AuthenticationPromptEventArgs e)
        {
            foreach (Renci.SshNet.Common.AuthenticationPrompt prompt in e.Prompts)
            {
                if (prompt.Request.IndexOf("Password:", StringComparison.InvariantCultureIgnoreCase) != -1)
                {
                    prompt.Response = Dts.Variables["$Project::sftpPassword"].Value.ToString();
                }
            }
        }

Bonus: Test with Docker

If you want to test locally with docker try this command line:

1

 docker run --name "MySFTP" -v C:\Temp\:/home/user/ -p 22:22 -d atmoz/sftp:alpine user:password:1001

Replace with your username and password and directory and you will be able to test locally :)

Have fun!!

 Share!