1

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;

enter image description here

Thanks

  • The user details will be encrypted so you won't see them in the connection string when you debug. That's my understanding. What error are you getting? – Molenpad Mar 27 '16 at 19:41
  • but the thing is that how can I connect to database now – Sukhjeevan Mar 27 '16 at 20:04
  • What error are you getting and when are you getting it? Can you post it please? – Molenpad Mar 27 '16 at 20:11
  • Loign failed for User ID "myid" but if instead of getting connection string value by Dts.Connections["Target_DB"].ConnectionString If I write down following code in .cs file I'm able to connect: string strConnection = "Data Source=ServerName;User ID=myid;Initial Catalog=product;Password:test@123;Persist Security Info=True;" – Sukhjeevan Mar 27 '16 at 21:06
  • I don't understand your update. Why did you add an app.config file? If that was based on my answer, you misinterpreted what I meant by Configuration Do you have more than one connection manager in your package/and or defined in your app.config? The Watch you're showing is going to access the first one, which may not be the one you are looking for. I think your question would be improved by providing more concrete details - what code have you written, what problem were you attempting to solve with it, etc. – billinkc Mar 28 '16 at 12:12
2

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

enter image description here

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.

  • I have some update in my post ...please check – Sukhjeevan Mar 28 '16 at 4:48
  • McDermid's post worked out for me...Now I am able to get password in connection string itself so I'm able to connect to database also. Thanks... – Sukhjeevan Mar 29 '16 at 2:16
-1

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

  • 1
    This is not correct. See in the attached screenshot I have my protection level set to "EncryptSensitiveWithUserKey" and yet the password is not accessible – billinkc Mar 28 '16 at 0:47
  • I think these properties are specific to Package password only these are not relevant to connection string information – Sukhjeevan Mar 28 '16 at 4:08
  • I never said the password was accessible, I said the package is able to decrypt it on runtime. @Sukhi, sorry your'e wrong. Those properties are specific to sensitive information, i.e. connection passwords. The package password is a different thing. The OP was asking for help making the connection, not viewing the fully qualified connection string. – Molenpad Mar 28 '16 at 8:30
  • @Molenpad: hanks for you response also... – Sukhjeevan Mar 29 '16 at 2:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.