FTP Connection string using expression in SSIS

Question

In my SSIS 2005 package, I need to give the FTP Connectionstring via an expression as I need to keep it configurable for the user from the dtsConfig file.

At the moment I tried giving the following expression:

Connectionstring = @[User::FTPServer] + "." + @[User::FTPUser] + "." + @[User::FTPPass]

For this unique syntax, I took pointers from the discussion at http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/3713e9a5-343a-4c5b-ac5d-1508cd5ab8be

My FTPServer variable also has port info in the format MYSERVERNAME:21.

But I am getting the error "530 User anonymous unknown"

Any idea how I can fix this error?

Answer 1

I think your variables should have two :: not one.

@[User::FTPServer] + "." + @[User::FTPUser] + "." + @[User::FTPPass]

EDIT:

You may be having some trouble due to the password being cleared

What I would do is set these details beforehand via a Script Task. Run the script and then run the FTP Task - I think that should that work.

Public Class ScriptMain

Public Sub Main()

Dim FTPConnectionManager As ConnectionManager

'Set variable to an existing connection manager
' Replace "FTP Server" with the name of your existing FTP Connection Manager
FTPConnectionManager = Dts.Connections("FTP Server")


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("FTPUserName").Value)

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


Dts.TaskResult = Dts.Results.Success

End Sub

End Class