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.

score 6 · Accepted Answer · edited Oct 01 '12 at 15:22

6

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

edited Oct 01 '12 at 15:22

Jon Seigel

12,251
8
58
92

answered Jun 25 '10 at 19:32

Dugan

908
2
13
30

4

Where to enable this? and what is this MSDTC? – Teju MB Jul 24 '13 at 08:38
2

@TejuMB It almost certainly depends upon what version of Windows you're running, so you'll need to Google that, but under Server 2008 R2: - Go to Start - Type Component Services - Select Component Services - Expand "Component Services" - Expand Computers - Expand "My Computer" - Expand "Distributed Transaction Coordinator" - Right Click "Local DTC" and click "Properties" That should contain what you need – Dugan Jul 24 '13 at 20:34
1

I enabled but its showing -"failed to restart MSDTC services,Please examine event log for details." – Teju MB Jul 25 '13 at 01:48

score 2 · Answer 2 · answered Dec 04 '14 at 16:59

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.

answered Dec 04 '14 at 16:59

Cozzaro Nero

129
1
8

score 1 · Answer 3 · answered Dec 04 '18 at 15:57

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

score 0 · Answer 4 · answered Sep 12 '19 at 14:51

I had a similar issue, but it was my own human error (tough week). I copy/pasted the Connections Strings into the Description column instead of the Value column in the Environment Properties. After moving them to the right (Value) column, my issue (naturally) had been solved.

score 0 · Answer 5 · answered Sep 16 '21 at 10:49

In our project scenario, windows service complied as 32 bit version but the package was designed with 64 bit version.

on deployment server, when windows service runs expects to have 32 bit library in GAC which was not installed. After investigation 64 bit installed only.

When windows service run, mentioned error comes up.

Possible solution is to complied the windows service into 64 bit only and verify.

Hope this finding may help you.

Happy programming.

SSIS Package failing with "Failed to acquire connection" error

5 Answers5

Linked