Zahid's blog on Data Engineering and BI

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.