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
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
