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 itdoes 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 immediatlyfrom 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 isc#examplecode for this issue. {........... string userName = "your usernameof the ftp"; string password = "Your passwordof the ftp"; etlPackage.Connections["FTP Connection Manager"].ConnectionString = "Data Source=MySQLServer;User ID=sa;Password=test;Initial Catalog=TestDbCatalog=TestDb;"<-- keep in mindno "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, Tryto set up a variable and set its value to the Connection expression in the FTP Task.=> right click on the FTP ConnectionManager 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'veused 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 weFTP frommultiple 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

Ok - I'll try it again. I think it was the script task that gave the error but I'll give it a go.

Thanks - that seems to work. Don't know what I had wong before. fyi - I am just setting the password in the script - the other values can be set on the connection. Not saying you shouldn't do everything in the script.

Within visual studio I have a password stored in the ftp connection. Everything works fine. When I add a configuration to store the ServerPassword in Sql Server the package fails within visual studio with password not authorized. I have tried several different protection options, none work. I have even tried storing the password in a configuration file. That also fails. I am connecting to a unix ftp site so I even tried all caps in the password. Still does not work. As long as the password is stored in the ftp connection manager the package works fine. Any help would be greatly appreciated. thanks

Edward Kim wrote: Here isc#examplecode for this issue. {........... string userName = "your usernameof the ftp"; string password = "Your passwordof the ftp"; etlPackage.Connections["FTP Connection Manager"].ConnectionString = "Data Source=MySQLServer;User ID=sa;Password=test;Initial Catalog=TestDbCatalog=TestDb;"<-- keep in mindno "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. Do I put this C# code in a separate class library? If so what do I need to reference to get it to compile?

Kutijevac, Thank you for he code sample, it helped me get my C# application working for setting the password. I have even created this blog post to help others consume this information quicker. http://blog.lyalin.com/2008/02/ftp-task-setting-password-through-code.html

Workaround to set it in script task helped: http://www.proteanit.com/b/2008/02/13/ssis-ftp-task-code-to-set-the-password/[I wonder what is the reasoning behind all this hassle of not being able to set ftp password with expression]

FYI - The problem with the expressions is that case is sensitive. You must specifiy @[User:: no @[user::

hi i am trying to do same thing in my task . i have a FTP task in the for each loop container and it works for each file. i have a sql task just above the ftp task that grabs the server ip , user name and password from the sql table . and i have set up a expression in the connection property of a ftp task but when i try to run it says that the connection is not found. i am confused while setting up the connection manager i have put default value for connection. @[User::FTP]+":21."+ @[User::User_NM_FTP]+"."+ @[User:assword_FTP] is the expression i have kept in a connection property. can any one help.

i have a same set up for connection prop but it does not work says this connection values cannot found. can u tell me how to set up connection manager for FTP dynamically from scratch. @[User::FTP]+":21."+ @[User::User_NM_FTP]+"."+ @[User:assword_FTP] is what i have used as expression.

Hello All,This posting has been helpful. I'm wondering if anyone can shed some light on how I can return the number of files copied to FTP? So how to return an integer value ranging from 0 (no files) to infinite (in reality20 files max).Thanks!

Thanks Clovernet.... it did work for me...192.168.1.1:21.Username.passwordthanks a bunch ...

Hi,since I faced the same problem I have done some tests.I reached the following two conclusions that are summarizing this thread:if you have to usea specificuser and a password (not the user anonymous user) you can follow one of the two options:1) - edit the Expression Connection String of the FTP Connection Manager as @[User::FTP]+":21."+ @[User::User_NM_FTP]+"."+ @[User:Password_FTP] - set the Protection Level of the package to EncryptSensitiveWithPassword (this is mandatory, the connection string setting is not enough)2) create a Script Task where you set the property ServerPassword of the FTP Connection Manager. (you can also set more properties but the same work could be done directly in the Expressions of the FTP Connection Manager component)Hope it helpsAntonio

Question - In ForEach Loop Container, how do I traverse throught the Files on the FTP? What do I set in the Expressions property to enter my FTP Server & Login?I tried enter the following in Directory property, but it didn't work.http://IP:Port.ServerName.PasswordThanks!

Let's go back and talk about the Config solution mentioned earlier. From the VS menu you select SSIS and then Package Configurations. (Hereyou select properties to export to a config file for setting at run time.)This step creates a Config file for your package and many properties that are not exposed in the Object UI are available for setting via the config file - including the ServerPassword property of the FTP Connection Manager. Scroll down until you find it and put a check mark in the box beside it.So using Package Configurations gives you a no-code method of getting at the property that was not exposed. I place my configurations in atable on SQL Server. I use the same table for all my packages.By the way, when you open the config file and locate the ServerPassword property in the file you will see that the passsword has been replaced by ***** You will need to enter actual password here and it will appear in clear text. Your password security then depends on the SQL table access you establish.RegardsRandy

Hi, i tried above example but getting the below error on execution: [Connection manager "FTP Connection Manager"] Error: An error occurred in the requested FTP operation. Detailed error description: The password was not allowed . Can any one please help in solving this problem?

HI Guys. I am using ssis to get files from FTP using FTP TASk which uses FTP connection manager. Package works fine if I save th user information in FTP conection manager editor and save it. I want to get the FTP conection ,user and passwrod infromation from .dstCnfig file . For this I have used the Expresion of FTP connection manager, and the property I have used is connectionstring . I am suplying this values "servername:portno.username.password". but when I run I get this error "Unable to connect to FTP server using "FTP Connection Manager" I have save the package with these information, dont save sentive informtion, save sensitive information with password and save sentive information with user key. None of them work. I have red for post that it should work with this servername:port.user.password. But I have this obervation as wall. if we provide all the all FTP information in the FTP connection manager editor and save it Now form the property window of FTP conection manager if we copy the contect of conectionstring it only has the information of server name:port no. no uername and password. Can any one help on this, Effort will be highly appricieted Thanks inadvance,

Just edit the configuration file with notepad; you need to insert the password in the XML configuration file, and save it. That's what worked for me, but we still have the issue of trying to run the package as a scheduled job on SQL Server 2008. This fails, so I created a scheduled task on the MS Server 2008 (os) to run the package, it runs fine... We are still trying to get a handle of why SQL Server 2008 can't run the task as a scheduled job! Hope that helps....