Miscellaneous Ramblings on Hacking

July 24, 2007

Automating FTP

Filed under: ETL, SQL Server Integration Services, SSIS — Zack Bethem @ 11:19 pm

Processing flat files are a common task when building a Data Warehouse. The need typically arises when integrating with 3rd-party providers or data outsourcers. A common integration technique is to use flat files with FTP as a transfer mechanism. One should use secure FTP, but SSIS out-of-the-box only supports the weaker, less-secure FTP.

When configuring the FTP connection, SSIS cannot store the login information. This is great if your final solution requires a human to push a button and enter the security information every time, but hardly ideal for us that like to automate things and sleep at night. To get around the problem, one must include a Script Task in the package’s Control Flow. The Script Task will set the connection properties of the FTP Connection automatically.

Here’s how you do it:

1. Add a Script Task within the Control Flow before the FTP

Sample Control Flow

2. Assign the following user defined variables as ReadOnlyVariables:

FtpServer – server connection

FtpPort – port number used for FTP connection

FTPuser – FTP user account

FTPpwd – FTP password for account

3. Add the following code to the Script Task to set the connection properties for the FTP Connection Manager:

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain

Public Sub Main()

Dim ftpConnectionManager As ConnectionManager

ftpConnectionManager = Dts.Connections(“FTP Connection Manager”)

ftpConnectionManager.Properties(“ServerName”).SetValue(ftpConnectionManager, Dts.Variables(“FtpServer”).Value)

ftpConnectionManager.Properties(“ServerPort”).SetValue(ftpConnectionManager, Dts.Variables(“FtpPort”).Value)

ftpConnectionManager.Properties(“ServerUserName”).SetValue(ftpConnectionManager, Dts.Variables(“FTPuser”).Value)

ftpConnectionManager.Properties(“ServerPassword”).SetValue(ftpConnectionManager, Dts.Variables(“FTPpwd”).Value)

Dts.TaskResult = Dts.Results.Success

End Sub

End Class

Advertisements

2 Comments »

  1. Hi Zack,

    We have recently implemented Secure-FTP (SFTP) task covering most of the standard FTP task functionality. You may give it a try if you want: http://www.cozyroc.com/products.html

    Regards,
    Ivan

    Comment by cozyroc — August 5, 2007 @ 7:08 pm

  2. Looks like an impressive library of components. Especially like the idea of an S3 connector. I’ll have to look into it when requirements guide me in that direction.

    Comment by Zack — August 13, 2008 @ 3:13 pm


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: