Setting the FTP password from external configuration sources in a SSIS package

SSIS(SQL Server Integration Service) is a great ETL(Extract Transform Load) tool from Microsoft.It has many useful features for integrating data from different sources.One of the handy feature is the "FTP Task" that enables downloading files from a remote FTP address. The "FTP Task" uses a "FTP Connection Manager" component that stores the FTP credentials(URL,User,Password..). Instead of hard coding the credentials into the component, we can use “Expressions” feature of SSIS to set those properties from configuration files,registry entries or environment variables. So we can set the FTP URL,port,user name from the configuration sources.Unfortunately and surprisingly there is no option in the “Expressions” list to set the FTP password.The “Server Password” option just does not appear in the “Expression” List.One work around of this problem is to use the “Script Task” component and set the “Server Password” property of the “FTP Connection Manager” component. The VB .NET code (SQL 2005 only supports VB .NET) snippet below does the following:

1.Accesses the “FTP Connection Manager” component.
2.Sets the “ServerPassword” property with the value of a package variable(“FTPPassword” here).The package variable can be set from any external configuration sources supported by SSIS(XML file,Environment Variable,Registry..).


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_Conn")


FTPConnectionManager.Properties("ServerPassword").SetValue(FTPConnectionManager,
Dts.Variables("FTPPassword").Value)

Dts.TaskResult = Dts.Results.Success

End Sub

End Class


Note:The “Script Task” component must execute before the “FTP Task” component .