Using WinSCP and SSIS to Download Files from SFTP

Recently, a client of the company I work for required us to download files using their SFTP server and process these files and for that, we decided that we would use SSIS to complete the task.

SSIS provides functionality to connect to a FTP server but not a SFTP server. Luckily, SSIS allows you to execute a process (in our case WinSCP), which we are going to use to download the files.

To complete this task, you will need;

• A copy of WinSCP
• SQL Server Data Tools
• A Script.txt file (explained later)

Firstly, you will need to open SQL Server Data Tools and create a new “Integration Services Project”. Once this has opened, you need to drag a “Execute Process Task” onto the Control Flow area.

Right click on the task and click on Edit, this will bring up the “Execute Process Task Editor”. In the Editor, you have the following options:

• General
  • Name
  • Description
• Process
  • RequireFullFileName
  • Executable
  • Arguments
  • WorkingDirectory
  • StandardInputVariable
  • StandardOutputVariable
  • StandardErrorVariable
  • FailTaskIfReturnCodeIsNotSuccessValue
  • SuccessValue
  • TimeOut
  • TerminateProcessAfterTimeOut
  • WindowStyle
• Expressions
  • Expressions

We will be amending the options in bold.

Set both the Name and Description Property to “Download from SFTP” .

The Executable property should be set to the location of the WinSCP install, in my case it was “C:\Program Files (x86)\WinSCP\WinSCP.exe”.

The Arguments property needs to be set to “-script=c:\Script.txt”. This is the location of a text file which holds the commands which WinSCP will execute. The script file I used is posted below.

option batch abort
option confirm off
open sftp://<User>:<Password>@<IPAddress>/ 
-hostkey=<HostKeyHere> --- e.g. stfp://root:password@123.456.789.2/ -hostkey="ssh-rsa AA:BB:CC:12:22"
get <PathOnServer>/<FileName> c:\<PathOnLocal> --- e.g. /home/Uploads/File.txt c:\SFTPDownload
exit

Finally, the Working Directory should be set to the folder in which WinSCP can be found, mine is set to “C:\Program Files (x86)\WinSCP”.

You should then be able to execute the task and the files which you have set in the Script to be downloaded, will be downloaded into the specified path.