Free SFTP for SSIS based on OpenSSH.

You got here because you have a sftp requirement for your SSIS and a third party component isn’t an option.


1. Download the latest libraries from http://www.tamirgal.com/blog/page/SharpSSH.aspx

2. The libraries at the time I downloaded them they didn’t have ‘strong name’ as the GAC complain about so you will need to follow instructions like : http://www.geekzilla.co.uk/ViewCE64BEF3-51A6-4F1C-90C9-6A76B015C9FB.htm

3. Once you have the libraries ready for the GAC then you need to registered them, I would strongly recommend to create an installer do it so you can deploy it easily on any environment but for now you can follow the next instructions:

a. C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Binn\gacutil /i c:\example\example.dll

b. copy the libraries to C:\windows\Assembly\

c. copy the libraries to Windows\Microsoft.NET\Framework\[.Net proper version]

4. Now we are 50% there. Create an ‘Script Task’ in your SSIS:

5. Go into design Script option and on the ‘class view’ do a right click and select ‘add reference’, you should see the libraries we have registered before, repeat this step for every single one of those.


6. Look for your OpenSSH formatted private key file .ppk, in my example the .ppk is in a folder and I have the path in a SSIS variable, I also hold the user and the sftp destination ip in other variables.

7. Set the SSIS ‘Run64BitRuntime’ property to false since these libraries are 32bits.

8. The following code is a sample to show you the basics you can do with the libraries which in my case are way enough:

Public Sub Main()

'Get variables values

Dim SFTPHost As String = CStr(Dts.Variables("cFtp").Value)
Dim SFTPUser As String = CStr(Dts.Variables("cFtpUser").Value)
Dim SFTPkey As String = CStr(Dts.Variables("cFtpKey").Value)

'Verifying S FTP

Try

Dim sftp As Tamir.SharpSsh.Sftp = New Tamir.SharpSsh.Sftp(SFTPHost, SFTPUser)
sftp.AddIdentityFile(SFTPkey)
sftp.Connect()

‘DO WHAT YOU NEED…

sftp.Close()

Catch ex As Exception
     Dim error_ As String = ex.Message
     error_ = error_ + ex.StackTrace
     If Not ex.InnerException Is Nothing Then
          Dim innerExc As Exception = ex.InnerException
          error_ = error_ + " : " + innerExc.Message + " : " + innerExc.StackTrace
     End If

     Dts.Events.FireError(-1, "", "*Unable to connect to Secure FTP: " + " : " + error_ + SFTPHost, "", 0)
     Exit Sub

End Try

Dts.TaskResult = Dts.Results.Success

End Sub

9. Once you are ready to create a SQL job to run your package be sure the owner and the runner profiles of the Jobs has enough permissions over the file system of the server were the job is running, especially to the SSIS working folder and the ‘C:\Documents and Settings\All Users\Application data\Microsoft\Crypto\RSA\MachineKeys’