Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

No SFTP Task Component in SSIS 2005/2008? No Problem!

By Stan Kulp,

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.

Resources:

SftpScriptTaskDemo.dtsx
Total article views: 12138 | Views in the last 30 days: 92
 
Related Articles
FORUM

Batch Variable in sqlcmd

Batch Variable in sqlcmd

FORUM

Variable not in script task

Package level variable not in dts.variables in script

FORUM

The USE command and variables

Trying to use the USE command with a variable

FORUM

Sql command not taking the packagevariable value

Sql command not taking the package variable value

FORUM

local package with batch file not running when using job

local package with batch file not running when using job

Tags
integration services (ssis)    
sftp    
ssh/tls    
vb.net    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones