Database connectivity issue in SSIS

Question

I have created a Connection manager in my SSIS project for database connectivity.I choose SQLClient Data Provider and select SQL server authentication mode for login.Test connection succeeded. In Script Task I'm using following to get the connectionstring:

string strConnection = Dts.Connections["Target_DB"].ConnectionString;

While debug I found following only:

Data Source=ServerName;User ID=myid;Initial Catalog=product;Persist Security Info=True;

As you can see password is missing and that's why I'm not able to connect to database server Even Test connection succeeded.

Does this means it's not saving password if so then how can I connect to database.

---------------UPDATE---------------------

I added App.config file in ScriptTask and added ConnectionString node in it.Then I tried to access connection string in following manner but couldn't succeeded and it's showing some other connection string not the one which I have written:

string strConnection = ConfigurationManager.ConnectionStrings["CRMConnectionString"].ConnectionString;

Thanks

Answer 1

You can't access the password in the connection string. It's a security measure, regardless of your package protection level.

If you need to use an existing connection, then you need to follow the examples laid out in Todd McDermid's post Use Connections Properly in an SSIS Script Task or in Matt Masson's Accessing OLEDB Connection Managers in a Script

Personal note, I never managed to get Matt's version working but since he was the PM on the SSIS team, I'll assume it's my failed implementation and not his.

An alternative for accessing the password, since this is tagged with 2008, is to use Configuration. Whether it be XML or SQL Server, store the connection string in an external repository and assign the value to an SSIS Variable. Then, apply an Expression to the Connection Manager's ConnectionString property. Within your script task, you'll be able to access the complete connection string which contains the password.

Answer 2

This happens because of the protection level set in the package properties. When you create a connection manager and test it, it doesn't save the username and password for runtime in the way you think it might.

To fix, set the pacakage protection level to EncryptSensitiveWithPassword or EncryptSensitiveWithUserKey (recommended for dev projects) and this should work.

At runtime, the connection credentials are encrypted, so if your protection level is something like DontSaveSensitive, you'll run into issues like this. EncryptSensitiveWithUserKey or EncryptSensitiveWithPassword will decrypt the credentials at runtime.

To change the package protection level, follow the instructions in this link