SSIS Package failing with "Failed to acquire connection" error

Question

We have an SSIS package that is launched from a web service. In the Dev environment, everything works fine, but in the QA environment I get the following error when trying to run the package: "Failed to acquire connection [ConnectionName]. Connection may not be configured correctly or you may not have the right permissions on this connection."

The connection uses SQL login, not integrated. The login itself has the appropriate privileges. The SSIS package is located on the file system, not on SQL server. I've used DTCPing and everything checks out fine between the SQL server and the Utility server. Unfortunately I know very little about SSIS itself, and am a bit of a loss as to what could be the problem. Any suggestions would be greatly appreciated.

Answer 1

I finally resolved the problem and it was related to my MSDTC settings. While MSDTC had been enabled, I had to enable the following settings:

• Network DTC Access
• Allow Remote Client
• Allow Inbound/Outbound
• Enable TIP

Answer 2

When using a "foreach loop" Container in a SSIS package, we had this error after processing 3K+ files... By setting the connection property RetainSameConnection to TRUE, we were able to move more than 32K files with no further issues.