The Problem

There is no SFTP (SSH/TLS-encrypted FTP) Task component in SSIS 2005/2008, but sometimes unencrypted file transfers are not acceptable.

The Solution

Use the open-source SFTP command-line client psftp.exe available from the puTTY download page: http://www.putty.nl/download.html

The Method

Have the SSIS package generate a dynamic batch file to run psftp.exe with a command-line parameter that references a dynamic FTP-command script also generated by the package, then run the batch file as a system process.

The VB.NET code to execute the system process looks like this:

The batch file looks like this:

The script file referenced in the psftp.exe command line looks like this:

The server domain, username, password, server subdirectory, script file path and source file path are dynamic fields populated by the SSIS package.

The SSIS SFTP Demo Package

Attached is source code for the SftpScriptTaskDemo.dtsx SSIS package shown in the screenshot below.

The package consists of two script tasks and eight global variables that allow values to be passed from the first task to the second. The next screenshot shows that the global variables in the first script task are read-write variables.

The following screenshot shows the global variables being loaded with values for the command-line paramters in the first script task.

In the real world, this script task would dynamically read the SFTP parameters from a table or file.

The next screenshot shows that the global variables in the second script task are read-only variables.

Here is the source code of the the second script task showing the psftp.bat file and script.sc files being generated using the values of the global variables, the batch file being run as a system process, and then the batch file and script file being deleted.

Script Task 2 Source Code

Running the Demo

Create the c:\psftp\ subirectory and copy the psftp.exe and source.txt files into it. Import the SftpScriptTaskDemo.dtsx SSIS package into a SSIS project and modify it. Actually running the demo requires access to an SFTP server and authentication credentials. The parameters in the first script task should be modified accordingly to match the credentials for your SFTP server. Once you have the correct credentials, you can run the package.