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
  • 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
  • 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
  • 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
  • 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
  • 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
  • I am getting same error.I checked scope too.

    The cotrol flow task that uses the connection manager not reflecting the updated values.

    This is causing login failed issue.

    I really wonder How people working with SSIS.Really.

    I am frustrated to work in this IDE as I need bomb diffuse kind of alert every time .

     

    • Edited by RevaTek Friday, July 11, 2014 9:10 AM
    Friday, July 11, 2014 9:08 AM
  • RevaTek,

    At what point is it failing? When you run the package in BIDS, via a SQL agent job etc?

    I understand that SSIS & BIDS can be frustrating to get used to but if you could give us as much information as possible perhaps we can ease those frustrations?

    What version are you running, can you provide a detailed example of the connection string, associated variables etc? 


    Ed Clarke | www.atheonanalytics.com

    Friday, July 11, 2014 9:25 AM
  • Hi ,

    Thanks for the quick reply.

    It fails when I try to edit the control flow task .that is SQL task -> Edit ->Build query 

    It says login failed.

    SQL task is using connection manager .The conn manager, It is not using the design time modified user id and password 

    I am using SQL server 2012 - sql server data tools.

    • Edited by RevaTek Friday, July 11, 2014 9:41 AM
    Friday, July 11, 2014 9:39 AM
  • So, firstly I'll declare that I'm still on 2008. However, you might find that you need to play around with a few things to get this to work. A couple of suggestions:

    1. Try saving the project - if you've not run it in debug mode since setting the username/password then that might be causing an issue
    2. Try opening the connection manager that is using the connection string  and then hit 'Test Connection' - does it succeed?

    Let me know how you get on


    Ed Clarke | www.atheonanalytics.com

    Friday, July 11, 2014 9:49 AM
    1. I tried saving the project - same issue
    2. I opened connection manager ,The password text box was empty.provided the same ."Test connection succeeded" Still then the sql task says ""login failed"

    Thanks,

    RevaTek

    Friday, July 11, 2014 9:58 AM
  • RevaTek,

    The connection manager will appear empty when you open it as all the relevant info is in an expression not directly inputted. Let me explain how I set things up in case you've missed something:

    1. Create new OLE DB connection (you have to point it at a server/database but we'll be overriding this in a minute
    2. Create a 'String' variable (I call it [ConnectionString] - make sure it's at the highest scope so it's accessible to the whole package
    3. Under the properties of that variable change [EvaluateAsExpression] to [True]
    4. Click the [...] next to [Expression] and then define your expression - here's an example: [Driver={SQL Server};Server=DB2\Instancename;Database=DB_test;user id=DB_test_Reader;password=nc72:eCz] - you could go one step further and have all the different parts of the expression slit out into variables
    5. Click on your connection that was created at step 1 and under the properties hit the [...] next to [Expressions]
    6. Using the drop-down menu, select [ConnectionString] and then hit the [...]
    7. Enter your variable name in the expression box - e.g. @[User::ConnectionString]

    I think that is about it!


    Ed Clarke | www.atheonanalytics.com

    Friday, July 11, 2014 10:13 AM
  • I created a new connection manager as you suggested.

    When I open new connection manager it has modified user name.

    When I changed this new connection manager in the sql task - and tried build query .Again same issue "Login failed " with the user name existing one [not the modified one].

    I guess I have to give up this task.

    Working with a software that has suspense in all its features is really troubling.


    Thanks,

    RevaTek


    • Edited by RevaTek Friday, July 11, 2014 11:16 AM
    Friday, July 11, 2014 10:51 AM
  • I created a new connection manager as you suggested.

    When I open new connection manager it has modified user name.

    When I changed this new connection manager in the sql task - and tried build query .Again same issue "Login failed " with the user name existing one [not the modified one].

    I guess I have to give up this task.

    Working with a software that has suspense in all its features is really troubling.


    Thanks,

    RevaTek



    Are you using sql authentication? Also whats the protection level used in the package?

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Friday, July 11, 2014 11:26 AM
  • You could also try just manually typing the SQL you want in the [SQLStatement] rather than using the [Build Query] button - that's what I always do - you might find that it all works fine when you run it!

    Also check that you have the correct permissions to the database


    Ed Clarke | www.atheonanalytics.com

    Friday, July 11, 2014 11:34 AM
  • Finally I am using manual type-verify .

    Any way ,Thanks a lot for your step by step explanation.

    Regards,

    RevaTek

    Friday, July 11, 2014 11:50 AM
  • RevaTek/Ed Clarke,

       I have the same problem. I am using Visual Studio 2012 (SQL Server Data Tool) and SQL Server 2012 database and other ODBC data source. I am trying to parameterize the server name etc.  for deployment and use environment variables is SSISDB catalog (project deployment mode).

       I created a simple SSIS package with two Database connections. When I put the username password, it works in debug mode and on server. However, it failed when I tried to use connecting string variable in expression which is evaluated right in design mode with the default values. The minute I changed the connection string to use the  expression, the connection became "offline" automatically. And the data source object in design mode became error element. I even tried to change the Connection property "DelayValidation" to True (by default it is false). The error came out as soon as I change the connection string to express (with right expression evaluated value).

         I am wondering how did you work around this.  Could you please provide me detail steps to resolve this?

         Thank you!
    KellyX

    Wednesday, July 16, 2014 7:17 PM