Password must be included in connection string for SQL server based configuration in SSIS

Recently, I have been working with SSIS configurations a lot. At the beginning, it seemed not much difference between XML configuration and SQL server configuration to me. Very quickly, I was proved wrong. In the configuration wizard, we can choose several properties of a connection; i.e. Connection String, User Name, Password, Server Name, Initial Catalog etc. When the xml file will be created by the wizard, you will see that the configured value for password is empty. So, u just have to open the xml file and set the password for yourself. And, things will work fine. In case you are wondering that the connection string itself can contain the password also, but SSIS does not store password in connection string. Yes, definitely u can open the xml file and write the password inside the connection string property value but SSIS will fail. It always tries to get the Password from the Password tag from the xml, not from the connection string.

My mistake was that I thought SQL server configuration reads the password the same way (from the password property of the connection). But, it does not. To my surprise, It looks for the password inside the connection string property. My package kept throwing the error: Connection may not be configured correctly or you may not have the right permission on this connection. My first step was, I opened the configuration table and went to the password property field and found the value for password “******” (encrypted). So, I wrote the real password instead of “******”. But, I kept getting the same error. After some nightmarish hours, I found what needed to be done and included the password in the connection string property. Then it worked finally.

Advertisements

About Zahid

Team Lead at IMS Health (www.imshealth.com). A part-time consultant and trainer. Fields of expertise are ETL, BI Reporting (Microstrategy, Excel, ASP.NET Dashboards etc.) Data Warehouse, OLAP Cube, MDX etc.
This entry was posted in SSIS (SQL Server Integration Service). Bookmark the permalink.

One Response to Password must be included in connection string for SQL server based configuration in SSIS

  1. Unknown says:

    Thank you Zahid

    This save me a lot of time.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s