ssis password package configuration

Question

I have an SSIS Package that I've created. I worked with SSIS a # of years ago so I'm rusty :).

I have 3 Connection Managers - DataWarehouse, DataWarehouse_PROD, DataWarehouse_TEST.

I am attempting to assign the connection string from the DataWarehouse_PROD or DataWarehouse_TEST Connection Managers to the DataWarehouse Connection Manager depending on another Package variable which could be PROD or TEST using a Script Task.

I've manually edited the password and connection string properties for each of the Connection Managers (per numerous forum entries stating that this works) in the dtsconfig file. I'm assuming I can then retrieve the connection string and password values from the loaded Package Configuration in the Script Task. I'm finding that the password is stripped out when I pull the Connection. Here is a snippet of my code:

string connectionString = Dts.Connections["ACORDDataWarehouse_PROD"].ConnectionString;
Dts.Connections["ACORDDataWarehouse"].ConnectionString = connectionString;

When I inspect the connectionString variable in the code, the password property is not part of the connection. If I manually add the password to the connection string in code, the connection is set correctly and I can retrieve data. From what I've read when using Package Configurations, the configuration is read when the Package is loaded into SSIS so these manual changes should be there.

I'm missing something . . .

Thanks for the assistance. Tom

Do you have the option of using windows authentication instead of sql authentication? — Nick.McDermaid
I prefer to use SQL authentication. Why, is there a difference between the 2 relative to SSIS? — Thomas DeVoe
With windows authentication you do not save a password anywhere, so you avoid all of those issues around package protection levels, sensitive data etc. — Nick.McDermaid
This sounds like an interesting approach. But this would be troublesome when developing since you have to be logged on to that AD account since SSIS would be using the AD credentials of the person logged on to connect to databases. This would be good if you can specify a different AD account to be used other then the one the user is logged into which I believe is not possible. — Thomas DeVoe
While developing you can use Run As when running the BIDS tool to run as someone else, which is admittedly a PITA. If running in SQL Agent you can use proxies. However in a 'mature' environment you would have a DEV database which all developers have windows access to (via a group) so it's not a problem. I dislike SQL accounts because once you give out the password to someone they can pass it around all they like, and you don't actually know who has access, and you can't revoke access to a single person, can't identify who is actually logged in — Nick.McDermaid

Thomas DeVoe Thomas DeVoe · Accepted Answer · 2014-12-18T15:02:13

I found a # of threads that talked about using Expressions. I created 2 Package variables - one for Environment and one for Password. I do have the ProtectionLevel set to EncryptAllWithPassword. For the Connection Manager that needed to be dynamic, I specified an Expression in the Expression Builder for the ConnectionString property which sets the value for either Test or Prod. This turned out to be a simple solution.

Thanks for the suggestions!

I wanted to add a few more comments to clarify my solution. Many times I find possible solutions but the write up is somewhat incomplete.

I ended up using indirect Package Configuration for the 2 variables I needed - Environment and Password. The expression picks up the values when the package is loaded. Below is the expression I used in the Connection Manager properties. Also, when you change the values in the indirect .dtsx file on a server, you have to redeploy the same package again for the change to take effect.

@[User::Environment] == "Prod"?"Data Source=ProdServer;User ID=ProdUsername;Password=" 
+ @[User::Password] + ";Initial Catalog=ProdDatabase;Provider=SQLNCLI10.1;Auto  
Translate=False;":"Data Source=TestServer;User ID=TestUsername;Password=" + 
@[User::Password] + ";Initial Catalog=TestDatabase;Provider=SQLNCLI10.1;Auto         
Translate=False;"

ssis password package configuration

1 Answers