SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER


How often have you received the error below?

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
Option 2 Multiple Properties

After using any of these 2 solutions the package executes fine

I Hope this helps and saves someone a bit of time…

Happy Coding

Advertisements

3 thoughts on “SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER

  1. Thank you so much! I had the exact same error and this fixed my problem. 9 out of 10 other sites just had people saying to change the Project properties Debug Options “Run64BitRuntime” to False and that fixed everyone else’s problems. Mine is set to False but I was still getting the error. Your post was the fix 🙂

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