SSIS Package failing with “Failed to acquire conne

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 had a similar issue. I was setting up a job to execute a SSIS package that I previously added to the SSIS catalogue on that server.

I solved it by going to the Job Step > Configuration > Connection Managers> For each connection I had to specify the Password and set RetainSameConnection = True.

I'm not sure that the RetainSameConnection is necesarry

Answer 2

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 3

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.