SSIS AcquireConnection method call failed

Question

_{Thursday, April 2, 2015 9:54 AM}

Hi I hope the Gods of SQL Server and SSIS can help me:

I Have some developers developing a SSIS package selecting data and moving it to a different environment, but they keep getting this error on one component.

[OLE DB Source [2]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "Servername.tablename" failed with error code 0xC0202009.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.

They think it is a DTC issue, but I am not sure as there are no differences between the Source and the destination.

Also within the package the data flow is in a container the data flow works fine when it is run without the container but not within. The Sequence Container has to be set to required for the Transaction Option.

So far i have tried the following:

1. Confirmed the DTC is set the same on both the Source and Destination Servers
2. Tried changing it to only run in 32 bit mode
3. Tried changing the Delay validation from Talse to True and Vice Versa

Any Suggestions would be appreciated

All replies (3)

_{Thursday, April 2, 2015 9:59 AM}

Any other error message prior to this error message?

Have you tested the SQL Connection being used by OLE DB Source by clicking on "Test Connection" button?

Ensure that credentials are correct and connection is successful.

Cheers,
Vaibhav Chaudhari
[MCTS], [MCP]

---

_{Monday, April 6, 2015 2:01 PM}

Hi,

Possible Causes

This message might appear because of one of the following problems:

• The connection string specifies a provider that is not supported.

• You do not have the correct permissions to use SQL Server Agent to run the package.

• You do not have access to the specified data source.

• The specified data source is not available for a variety of reasons, depending on the type and location of the data source. For example, a data source might not be available for the following reasons:

  • The network is not operating.

  • The server is not available.

  • Another application is using the data source file.

• The connection manager is configured to use SQL Server Authentication, but the package configuration file does not contain the needed password.

Above causes are listed from MSDN in below URL:-

https://technet.microsoft.com/en-us/library/cc627376%28v=sql.105%29.aspx

In above URL, user troubleshooting also there.

Thanks

Prasad

---

_{Tuesday, March 20, 2018 12:52 PM}

On my SSIS package when we moved to new server, I receive the error message. after repointing server connection, and successfully testing the connection...it still showed red X's.  When I right-mouse clicked on my connection manager inside my package, the Work Offline was checked. I removed the check and everything worked.