SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER

How often have you received the error below?

Published in

Eugene Niemand’s Blog

3 min readJul 20, 2012

Error

SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager “Ovott” failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

Very helpful, I know… Please excuse the sarcasm. I spent days trying to fix this error as I just installed some database drivers before building a new package. At first I thought it was the drivers, so I removed them. Long story short It cost me Repairing/uninstalling and reinstalling SQL Server 2008 R2 from different images around 9 times before I could get SSIS to open packages again, but this is a whole other story.

So after getting SSIS working again I thought, hey everything is back to normal. Tried executing the package and to my surprise I was still getting this error.

Problem

I have to mention that this error only occurs when you execute the package, when you do a preview everything works which makes it even more confusing, but this makes sense as configurations only apply when you execute the package.

The error was due to using Package Configurations for my Connection Managers incorrectly.

Here is a preview of the results when I click preview.

In the the configuration settings you can specify what properties you want to store to the database (or other media). When I initially set it up I checked the parent check box next to the connection which in turn checks all the check boxes below it.

Now this works fine when your connection manager uses Integrated Authentication, however when you are using SQL Authentication and you select all properties it will give you the above mentioned error.

When you execute the Package/Task this is what it looks like

Solution

You need to either select connection string by it self or select a combination of Server, User, Password and other properties if required but don’t select connection string. When selecting all the properties I think they somehow cause a conflict and cause the above error. Here are two solutions:
Option 1 Connection String