none
FTP Connection Manager: Set FTP password using a variable RRS feed

  • Question

  • 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

    Wednesday, November 2, 2005 6:30 AM

Answers

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

    Thanks
    Wednesday, November 2, 2005 4:37 PM

All replies

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

    Thanks
    Wednesday, November 2, 2005 4:37 PM
  • 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?
    Friday, February 17, 2006 3:57 PM
  • 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

     

    Friday, February 17, 2006 5:11 PM
    Moderator
  • Jamie,

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

    Saturday, February 25, 2006 1:03 AM
  • 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!
    Thursday, March 16, 2006 8:34 PM
  • 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.

    Friday, April 28, 2006 1:33 PM
  • 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.




     

    Friday, August 25, 2006 4:19 PM
  • Many thanks. I got the message "cast to object and then use SetValue" und will try it at the next opportunity.
    Monday, September 4, 2006 3:10 PM
  • 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

    Tuesday, September 12, 2006 2:41 PM
  •  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]

    • Proposed as answer by stunugun Friday, October 9, 2009 5:17 PM
    Wednesday, November 29, 2006 4:18 AM
  • 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

     

     

    Tuesday, January 2, 2007 4:29 PM
  •  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!!!

     

    Wednesday, January 3, 2007 12:42 AM
  • 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.

    Tuesday, January 16, 2007 12:02 PM
  • 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

    Tuesday, January 16, 2007 12:33 PM
  • Ok - I'll try it again. I think it was the script task that gave the error but I'll give it a go.
    Tuesday, January 16, 2007 1:42 PM
  • 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.

    Tuesday, January 16, 2007 6:20 PM
  • 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

    Tuesday, April 24, 2007 7:28 PM
  •  Edward Kim wrote:

    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.




     

     

    Do I put this C# code in a separate class library? If so what do I need to reference to get it to compile?

    Tuesday, October 30, 2007 2:21 AM
  • 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

    Wednesday, February 13, 2008 11:27 PM
  • 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]


    Thursday, March 27, 2008 7:07 PM
  • FYI - The problem with the expressions is that case is sensitive. You must specifiy @[User:: no @[user::

     

    Friday, May 30, 2008 6:35 PM
  • 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:Stick out tongueassword_FTP] is the expression i have kept in a connection property.

     

    can any one help.

     

    Friday, June 13, 2008 3:35 PM
  •  

    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:Stick out tongueassword_FTP] is what i have used as expression.

    Friday, June 13, 2008 3:38 PM
  • 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 reality 20 files max).

    Thanks!
    Wednesday, August 5, 2009 12:03 AM
  • Thanks Clovernet.... it did work for me...
    192.168.1.1:21.Username.password

    thanks a bunch ...

    Friday, October 9, 2009 5:18 PM
  • 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 use a specific user 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 helps

    Antonio

    • Proposed as answer by AntonioCH Tuesday, October 20, 2009 9:45 AM
    Tuesday, October 20, 2009 9:45 AM
  • 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.Password

    Thanks!
    Thursday, October 22, 2009 2:35 PM
  • Let's go back and talk about the Config solution mentioned earlier.

    From the VS menu you select SSIS and then Package Configurations. (Here you 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 a table 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.

    Regards
    Randy
    Tuesday, November 3, 2009 3:57 PM
  • 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?

     

     

     

    Friday, June 4, 2010 8:35 AM
  • 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,

     

    • Proposed as answer by TheHose Tuesday, February 15, 2011 6:32 PM
    Thursday, August 5, 2010 1:33 PM
  • 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....

    Tuesday, February 15, 2011 6:38 PM
  • 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

     

    But SSIS won't let me save that password permanently after I add it in the connection manager :( How do I overcome that ? I am thinking of just using a C# FTP script instead to make it painless.

    Wednesday, January 1, 2014 12:16 AM