none
SSIS: How to use variable in Connection Manager's Properties

    Question

  • Hello Gurus,

    How to use variables in Connection Manager's properties? I see some replies through Configuration Package. But what if, it is still in development stage? I mean, can I use the Variable tab and create some variables like User::DBUserNameSource, User::DBPasswordSource, User::DBuserNameDestination, User::DBPasswordDestination, then put them in Password and UserName property of Connection Manager?

    If this is possible,  how and how can I set the values of those variables I mentioned when I am going to deploy the package in the Production?

    Hope someone could help on this. Thanks in advance

     

     

    Tuesday, August 24, 2010 8:15 AM

Answers

All replies

  • Hi,

    You need to use expressions. Put an expression on the properties that you would like to change and build up an expression that uses your variables. there are tonnes of resources out there devoted to SSIS expressions: http://www.bing.com/search?q=ssis+expressions&src=IE-SearchBox&FORM=IE8SRC

    Also, I recommend that you only have one variable User::ConnectionString rather than a variable for each constituent part of the ConnectionString.

    -Jamie


    http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me
    Jamie Thomson
    Tuesday, August 24, 2010 8:26 AM
    Moderator
  • Hi Jamie,

    Thanks for the quick reply. I have still followup question, If I will use one variable for connection string, I notice that, the connection string the SSIS produced doesn't have password ex: Data Source=MyServer\Server_112;User ID=sa;Initial Catalog=Chinook;Provider=SQLNCLI.1;Auto Translate=False; how can I put the password?

    Tuesday, August 24, 2010 8:34 AM
  • Hi,

     

    This is an example of a connection string with a password which works in my environment:

    Driver={SQL Server};Server=DB2\Instancename;Database=DB_test;user id=DB_test_Reader;password=nc72:eCz


    Ed Clarke | www.atheonanalytics.com
    • Marked as answer by lovenuqui Thursday, August 26, 2010 2:11 AM
    Tuesday, August 24, 2010 8:42 AM
  • Hi Jamie,

    Thanks for the quick reply. I have still followup question, If I will use one variable for connection string, I notice that, the connection string the SSIS produced doesn't have password ex: Data Source=MyServer\Server_112;User ID=sa;Initial Catalog=Chinook;Provider=SQLNCLI.1;Auto Translate=False; how can I put the password?

    You could type it yourself.

     

    Note that, by default, SSIS will not store freetext passwords within a .dtsx file. You have to either encrypt the package or pass them in at execution-time; your choice of what you do depends on the ProtectionLevel property of your package. Having said that, this becomes irrelevant if you are using expressions (which you are).


    http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me
    Jamie Thomson
    Tuesday, August 24, 2010 8:48 AM
    Moderator
  • Hi edtclarke,

    Thank  you for your input.

    I tried your suggestion. I manuallly typed what you have typed in here in my connection manager's property CONNECTIONSTRING, unfortunately, it doesn't accept the password. I mean, the password didn't display.

    Tuesday, August 24, 2010 8:54 AM
  • Hi edtclarke,

    Thank  you for your input.

    I tried your suggestion. I manuallly typed what you have typed in here in my connection manager's property CONNECTIONSTRING, unfortunately, it doesn't accept the password. I mean, the password didn't display.

    No, as I said SSIS will not store passwords except in an encrypted format. However, if you type it into the variable that holds your connection string then you will circumvent the problem.
    http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me
    Jamie Thomson
    Tuesday, August 24, 2010 8:56 AM
    Moderator
  • Sorry, this doesn't sit directly in the connection string, it is a variable which is then referenced in an expression - forgot to mention that Jamie, so completely agree!
    Ed Clarke | www.atheonanalytics.com
    Tuesday, August 24, 2010 10:17 AM
  • Dear Jamie and Ed Clarke,

    Thank you ver much for you quick response. I am truly grateful.

    Unfortunately, I am not done yet with my problem :(.

    These are the things that I have done so far:

    1. Created a Variable User::DBConnectionString

    2. From my OLE DB  Connection Manager's Property, Clicked the Expression ellipses button, Property Expressions Editor popped, from Property(Column), I chose ConnectionString

    3. Clicked the ellipses button in Expression(Column), Expression Builder popped up.

    4. Expanded the Variables, only System variables displayed. Cannot find my variable.

    Am I doing the wrong thing? Hoping for your patience.

    Regards,

    Wednesday, August 25, 2010 2:24 AM
  • Hi Gurus,

    My user defined variable has a scope of Data Flow task :-) it should've been Package :-) I'll let you know when I'm done.

    Wednesday, August 25, 2010 5:14 AM
  • Make sure you set the 'EvaluateAsExpression' property of the variable to 'True' as well
    Ed Clarke | www.atheonanalytics.com
    Wednesday, August 25, 2010 8:39 AM
  • Dear Jamie and Ed Clarke,

    Thank you ver much for you quick response. I am truly grateful.

    Unfortunately, I am not done yet with my problem :(.

    These are the things that I have done so far:

    1. Created a Variable User::DBConnectionString

    2. From my OLE DB  Connection Manager's Property, Clicked the Expression ellipses button, Property Expressions Editor popped, from Property(Column), I chose ConnectionString

    3. Clicked the ellipses button in Expression(Column), Expression Builder popped up.

    4. Expanded the Variables, only System variables displayed. Cannot find my variable.

    Am I doing the wrong thing? Hoping for your patience.

    Regards,

    Check the scope of your variable, make sure it is scoped to the package.
    http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me
    Jamie Thomson
    Wednesday, August 25, 2010 9:19 AM
    Moderator
  • Hi Gurus,

    My user defined variable has a scope of Data Flow task :-) it should've been Package :-) I'll let you know when I'm done.

    Oh. Cool. :)
    http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me
    Jamie Thomson
    Wednesday, August 25, 2010 9:19 AM
    Moderator
  • Hello again Gurus!

    It went well =) thanks again for helping me out.

    By the way, I have another question, but I will post it to a different Thread. Hope you could help me still.

    Thanks again and more power to you both!  :)

    Thursday, August 26, 2010 2:13 AM