SSIS and your SFTP options

The SQL Server intergration Services (SSIS) has always had a FTP task but it is odd that it has never had an SFTP task (up to 2017). SFTP is the Secure File Transfer Protocol using the Secure Shell SSH. There are third party SFTP tasks that can be deployed to your Server running SSIS but the simplest way to use SFTP is through an execute process task. Here are three options:

SFTP using the PSFTP

This uses the Execute Process Task to fire PSFTP to do the SFTP transfer. This is the most common solution that I have encountered and is documented in this mssqltips post. In step 1 of the post, the text is correct but the diagram is incorrect, you should download psftp.exe.

The post also explains all the subtlties of SQL Agent authetication, you have to ensure that the SSIS job has sufficient permissions to execute the psftp executable and be able to write the downloaded file to a given location. A failsafe way to ensure you have the permissions set up correctly and all the SSH certificates in place on your SSIS server (Windows Server) is to open a command window with a right click and "run as different user". Select the user account that will actually run the task as the scheduled SSIS process (i.e. the agent service account or appropriate proxy account). Then simply run the batch file in the command window. Here you can directly fix any permission or certificate issues. Once this runs through okay - you are guaranteed that the SSIS task will have the correct local configuration.

SFTP using WinSCP

This is exactly the same approach, but using the excellent WinSCP software which is free and is a useful file transfer tool on Microsoft machines.

The advantage is that WinSCP can automatically write a batch script including the certificate management. Simply use WinSCP to transfer the given files, then at the point of performing the transfer there is a "Transfer settings…" button dropdown. This will give you the option to "Generate Code…"

WinSCP generate scripts

From here you can write a batch file, Command Line or PowerShell Script. All of which can be used in an Execute Process Task identical to the previous PSFTP method.

SFTP third party open source projects

TLDR: I played around with this option but couldn't get it to work reliably.

(Note: there are also supported third party apps that you can buy and install on your server.)

On codeplex, before it was shutdown, there was an open source project that is still available in the archive. I found the same code on github (https://github.com/vivarius). There is an email address in the code if you want to contact the developer directly, I am not the author.

I looked at the project SSISFTP2014 in github. It is straightforward to clone, compile, add to the GAC and make a copy of the SSISSFTPTask130.dll and put it into C:\Program Files (x86)\Microsoft SQL Server\140\DTS\Tasks (for my test SQL 2017 box) - the code base references are a little messy but I used VS2017 to compile it with minimal fuss. Once the dll is in place and added to the GAC you then get an SFTP task in your SSIS toolkit.

SFTP task in SSIS Toolbox VS2017 SSDT

Here are the SFTP Task properties.

SFTP Task Properties

On testing, it worked well from my local test Linux SFTP server but when I pointed it at one of my "real" data source servers the key exchange (KEX) failed. The logs state that the server rejected the SFTP connection because the KEX protocols used in this SFTP Task project are considered weak and no longer supported by the SFTP server (use "ssh -T | grep kex" on your SFTP server to list the installed key exchange algorithms). It would require me to investigate and change the KEX protocols in my SFTP Task, test and redeploy. So be aware that this is not a production ready task. Still, it would make a nice project if only there where more hours in the day!!

Conclusion

Go with an Execute Process Task. Also, it couldn't be simpler with WinSCP because it will write the batch file for you.

<< Go back to the previous page