The AcquireConnection method call failed with error code 0xC0202009.

When deploying a SSIS package from pre-production to production system I get a message “The AcquireConnection method call failed with error code 0xC0202009.” What I know is that this particular error 0xC0202009 is related to a login problem. I have a login in my SQL Server, which I am using in connection with the SSIS package. The SSIS package needs to “know” the username and the password for this login in order to get data to and from the database.

The solution is to modify the data connections of the package as follows:
Data Source=ServerName;User ID=UserName;password=Password;Initial Catalog=DatabaseName;Provider=SQLNCLI10.1;Persist Security Info=True;Auto Translate=False;Application Name=SomeApplicationName; As you can see, in the connection string I specify the password.

Don’t be alarmed if you specify the password, save the changes and then the password is not showing when you come back. This is for security reasons. Here is how the configuration string will look the next time you come to this screen: Data Source=ServerName;User ID=UserName;Initial Catalog=DatabaseName;Provider=SQLNCLI10.1;Persist Security Info=True;Auto Translate=False;Application Name=SomeApplicationName; So, the bottom line is – specify the password in the connection string, and this error will not happen. (There are 2 places where you can specify the connection string: either when you run the SSIS package in debug mode from the SSIS server, or in the SQL Server Agen job which is executing the package).

Feodor Georgiev