Error connecting to FTP server using SSIS FTP task

SSIS FTP task does't allow user to connect to different FTP servers. If we store FTP connection sting (Example: ftp.xxx.com:21.username.password)in Config file also it will accepts domain name, port and username but it wont accept password. Also FTP task has not exposed the FTP password property. To over come this problem we have to override the FTP connection manager itself. So that it accepts connections dynamically.

Public Sub Main()
        Dim vars As Variables
  
        Dim ftpConnectionManager As ConnectionManager
        ftpConnectionManager = Dts.Connections("FTP Connection Manager")

        ' ADDED TO MAKE FTP CONNECTION STRING DYNAMIC
        Dim FtpServer As String
        Dim FtpPort As String
        Dim FtpUser As String
        Dim FtpPassword As String

        Dts.VariableDispenser.LockOneForWrite("FtpServer", vars)
        FtpServer = Text.Encoding.Unicode.GetString(Convert.FromBase64String(vars("FtpServer").Value.ToString().Trim()))
        vars.Unlock()

        'MsgBox("FtpServer = " + FtpServer)

        Dts.VariableDispenser.LockOneForWrite("FtpPort", vars)
        FtpPort = Text.Encoding.Unicode.GetString(Convert.FromBase64String(vars("FtpPort").Value.ToString().Trim())) 'vars("FtpPort").Value.ToString().Trim()
        vars.Unlock()

        'MsgBox("FtpPort = " + FtpPort)

        Dts.VariableDispenser.LockOneForWrite("FtpUser", vars)
        FtpUser = Text.Encoding.Unicode.GetString(Convert.FromBase64String(vars("FtpUser").Value.ToString().Trim())) ' vars("FtpUser").Value.ToString().Trim()
        vars.Unlock()

        'MsgBox("FtpUser = " + FtpUser)

        Dts.VariableDispenser.LockOneForWrite("FtpPassword", vars)
        FtpPassword = Text.Encoding.Unicode.GetString(Convert.FromBase64String(vars("FtpPassword").Value.ToString().Trim())) ' vars("FtpPassword").Value.ToString().Trim()
        vars.Unlock()

        'MsgBox("FtpPassword = " + FtpPassword)

        Dts.Connections("FTP Connection Manager").Properties("ServerName").SetValue(ftpConnectionManager, FtpServer)
        Dts.Connections("FTP Connection Manager").Properties("ServerPort").SetValue(ftpConnectionManager, FtpPort)
        Dts.Connections("FTP Connection Manager").Properties("ServerUserName").SetValue(ftpConnectionManager, FtpUser)
        Dts.Connections("FTP Connection Manager").Properties("ServerPassword").SetValue(ftpConnectionManager, FtpPassword)

        Dts.TaskResult = Dts.Results.Success

End Sub

By Cos mos   Popularity  (4424 Views)