FTP Connection Manager: Set FTP password using a variable

Hi,

I want to setup an FTP task that is portable in the sense that all it's connection parameters are stored in variables.

I can create expressions for properties ServerName and ServerUserName but not the ServerUserpassword. Is there an property that I missed, there seems to be properties for everything else except the key piece of info, the password.

I could create a script component using the FtpClientConnection method as a last resort but would rather not.

Any ideas?

Thanks

Ignore this thread, I wasn't thinking straight last night. I can use a config file to dynamically set the password.

Thanks|||I am trying to have the package load the password for an ftp site from a configuration file like you did. I cannot get it to work. How did you get it to work? Because it is sensitive information it does not get saved to the configuration file. I tried setting the package protection level to 'EncryptSensitiveWithPassword'. What did you use for the package protection level? Where are you storing you configuration file: SqlServer table or file system?|||

ProtectionLevel does not have any effect if the password is stored in a configuration file.

The password does not get stored in the configuration file by SSIS as this was considered a security risk. You have to add it there yourself. That way you, not the software, are accountable for storing it there.

-Jamie

|||

Jamie,

Thanks. As usual your answer was spot on. Cheers.

|||I'm a newbie at SSIS. I still don't understand from this thread, how to set the password. I have a list of ip/user/pass values in a table. How can I set the password on the connection manager dynamically?

Thanks in advance!
|||

Same Problem here. It looks like a password can be set only indirectly by setting the full ConnectionString. For example the following script

Public Sub Main()

Dim conn As ConnectionManager = Dts.Connections("testConn")
conn.ConnectionString="Data Source=MySQLServer;User ID=sa;Password=test;Initial Catalog=TestDb;Provider=SQLNCLI.1;"

Dts.TaskResult = Dts.Results.Success

End Sub

can modify an existing SQL Server Connection including the password. Note the password is removed immediatly from the string after setting it this way, but it seems it is still stored internally.

In an analogous way it should be possible to modify an existing FTP Connection. Sadly, I don't know the syntax for that type of connection.

MsgBox'ing the ConnectionString for an FTP Connection looks like 168.192.1.2:21 and does not even include the ServerUserName.

|||

Here is c# example code for this issue.

{
......
.....
string userName = "your username of the ftp";
string password = "Your password of the ftp";

etlPackage.Connections["FTP Connection Manager"].ConnectionString =
"Data Source=MySQLServer;User ID=sa;Password=test;Initial Catalog=TestDbCatalog=TestDb;" <-- keep in mind no "Provider"
object ftpConnectionManager = (object)etlPackage.Connections["FTP Connection Manager"];
etlPackage.Connections["FTP Connection Manager"].Properties["ServerUserName"].SetValue(ftpConnectionManager, userName);
etlPackage.Connections["FTP Connection Manager"].Properties["ServerPassword"].SetValue(ftpConnectionManager, password);
.......
.......
}

Then it will work.
If you need more information, let me know.
Pls,,, keep in mind, if you include "Provider" and "Auto Translate" info items within the connection string, you will get error.

|||Many thanks. I got the message "cast to object and then use SetValue" und will try it at the next opportunity.|||

Thanks for this thread, it has helped a lot.

I set the password in the config file manually as suggested, but now I get this error: The password was not allowed.

What is causing this and how do I fix it?

Thanks for your help.

Randy

|||

Hi,

Try to set up a variable and set its value to the Connection expression in the FTP Task.
=> right click on the FTP Connection Manager and then select edit. Add a new expression for Connection and set it to the variable you have set up to store this value.This variable can be changed at runtime using Package Configurations.

Expression - ConnectionString -

"Server IP: Port.loginAccount.loginPWD"

EX: "192.168.1.1:21"+ @.[user::FtpAcct]+"."+ @.[user::FtpPwd]

|||

Hi,

Can you double check this syntax, please? It looks like that example doesn't match syntax provided!

I have tried several combinations of this parameters but neither didn't work! Did you get solved this problem? What else need to be set to get this concept working?

I can't beleive that MS didn't publish standard solution on such a genuine common requirement? Again!!!

Thanks Forward

Sladjan

|||

clovernet wrote:

Hi,

Try to set up a variable and set its value to the Connection expression in the FTP Task.

=> right click on the FTP Connection Manager and then select edit. Add a new expression for Connection and set it to the variable you have set up to store this value.This variable can be changed at runtime using Package Configurations.

Expression - ConnectionString -

"Server IP: Port.loginAccount.loginPWD"

EX: "192.168.1.1:21"+ @.[user::FtpAcct]+"."+ @.[user::FtpPwd]

Example should be "192.168.1.1:21." + @.[user::FtpAcct]+"."+ @.[user::FtpPwd] (it's missing the period after the port)

I've used the above and it works great. I would have NEVER figured this out without someone posting (THANK YOU CLOVER!!!)... Actually got all variables from a table through an SQL task that reads a view of FTP Info and directories/files to ftp. Since we FTP from multiple servers and multiple files/remote directories, we just put the FTP Task in a For Each loop. Did the concatenation for the ConnectionString in the FTP Connection manager as clovernet suggested @.user::varFTPIPAddress] + ":21." + @.[user::varFTPUser]+"."+ @.[user::varFtpPwd]

My issue is that on the FTP task, if I pass it "\directory\subdirectory\filename.ext" for the remote path (from a variable), I get an error message that the remote path doesn't start with a "/". When I reversed the slashes, it worked fine.... Why can the local/destination path have back slashes ("\"), but the remote/source path must have forward slashes? What the heck!!!

|||

I can't get the connection string to work. I can use a script task to use the variables to perform FTP operations but the only way I can set the password in a connection is via the config. It gives the error "Password not available" whenever I try to set it - that's in an expression or script setting the configuration string (as above) or in a script task setting the serverpassword. I'm guessing it's because it tries to read the value before setting it. Anyone know a way around this?

I need the password in a variable for the script tasks for thnigs that can't be done with an ftp task but the mget is easier in the ftp task. At the moment it requires two config entries for the same value.

|||

Dan,

I have tried a several combination of that example (including a period you are suggesting) but didn't get it working. It has to be sometjing else on the configuration to prevent pasing of the connection string on a same manner as you and Clover were suggesting. Thanks guys for your contributions, anyway.

Dan, try to use "/" for source and target destinations, both. Windows is able to handle both of them and you would not have a problem on ftp server and would not bring you confusion.

-

Nigel, here is what I have done and got it working by using package variables and VB.NET script task. It is more/less around few ideas sugested above:

Firstly I declared the set of variables for each of the relevant ftp parameters. Than prior FTP task I set following script task passing all of those variables as parameters.

Public Sub Main()

Dim ftpConnectionManager As ConnectionManager

ftpConnectionManager = Dts.Connections("FTP Connection Manager")

Dts.Connections("FTP Connection Manager").Properties("ServerName").SetValue(ftpConnectionManager, Dts.Variables("FtpServer").Value)

Dts.Connections("FTP Connection Manager").Properties("ServerPort").SetValue(ftpConnectionManager, Dts.Variables("FtpPort").Value)

Dts.Connections("FTP Connection Manager").Properties("ServerUserName").SetValue(ftpConnectionManager, Dts.Variables("FtpUser").Value)

Dts.Connections("FTP Connection Manager").Properties("ServerPassword").SetValue(ftpConnectionManager, Dts.Variables("FtpPassword").Value)

Dts.TaskResult = Dts.Results.Success

End Sub

Than remove values from the all non mandatory fields on FTP connection manager and disable validation.

On FTP task itself I parametrized destination folder (by using "/" inside the string) and source file connection.

All together, for each ftp server and each registered file I got FTP running in a loop sending each file on different designated server. And it works!

Sladjan