SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER

Question

SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply

Marie Kelly SSC Enthusiast Points: 100 More actions · Answer 1

Ok, I am going to sound like a useless msdn moderator here, but just bear with me.

A couple questions on the execution: are the packages running with a package protection level like DontSaveSensitive or EncryptWithUserKey? Those settings have their own special personalities, and they may work in debug but totally different in SQL Server as a scheduled job. Now I have learned that the AcquireConnection... error is more generic than I once thought: usually SSIS didn't find the password for that user name (wasn't saved in the Connection Manager).

EncryptWithUserKey is a setting that will not allow a package to execute UNLESS it was run by that user (security context of Active Directory account) on the machine it was created. See this explanation: http://msdn.microsoft.com/en-us/library/ms141747.aspx Useless for any kind of multi-server environment.

Then there is DontSaveSensitive protection setting: it wants to use a configuration file. That is a major project in SSIS, of course, with little documentation behind it. But try the Package Configuration Organizer which will help make the config file for you. Then you need to deploy it properly (no build errors in Visual Studio) if you want it to work as a scheduled job in SQL Server Agent Service. If you don't deploy correctly the job will appear to execute the SSIS package successfully, but if you look carefully in the job history text, you will notice errors like 'Failed to decrypt protected XML node "DTS:Password"' Anyhow, I mention that because it is common to have the execution results in SQL Server not match the results of debugging in Visual Studio using configuration files, with the error text you mention: 'AcquireConnection ... failed...'

Finally, check the version of SSIS, not SQL Server Database Engine, but the Integration Services component, installed once per machine, not instanced like other MSSQL Server products. Make sure Integration Services has the latest service pack that matches the version of SSIS project types you are editing in Visual Studio. If it is more recent, no problem, but if it is older, expect some issues trying to run new package versions on old version of SSIS server.

Sorry for the huge ramble. Hope it helps.

4x4krzy Ten Centuries Points: 1173 More actions · Answer 2

All great info.

I changed the encryption to none, but it made no change.

What I did find out is the SQL Agent was running under the Local System and it would not run it. I changed the agent to use my username and it worked just fine.

I now need to create a new service account with the proper privileges and it will run just fine.

Thanks much

sudhakara SSCrazy Eights Points: 9425 More actions · Answer 3

Padraig FItzgerald (10/27/2010)
Hi, I had the same problem and found a different solution. Posting it here in case it helps someone else out...
Dev Environment BIDS 2008 on Win 7 64 Bit. Source: Oracle 10 DB. Dest :SQL2000 DB x86.
I was getting the error mentioned here and tried the various solutions posted.
None worked but i found that going to Project-->Properties and setting the 'Run64BitRuntime' to 'false' worked.
cheers
Padraig

Thanks , It worked for me too.

santhosh_k_gadepally SSC Rookie Points: 45 More actions · Answer 4

santhosh_k_gadepally

SSC Rookie

Points: 45

December 7, 2012 at 7:14 am

#1566606

Thanks a lot Padraig, it working for me:)

singhster Ten Centuries Points: 1387 More actions · Answer 5

singhster

Ten Centuries

Points: 1387

February 22, 2013 at 2:37 am

#1590245

Thanks Padraig, that worked for me too.

rderisala1 SSC Veteran Points: 257 More actions · Answer 6

Thanks a lot Padraig... Worked for my case as well.

ljcarson Valued Member Points: 65 More actions · Answer 7

Using the hints to set Project Properties Run64BitRuntime = FALSE, the SSIS pkg did run, but instead of the 17,622 rows that the SSIS Data Flow module shows entering the OLEDB Destination module, it only wrote out 762 rows to the SQL table?? Shows no errors either??

Under Progress tab:

[SSIS.Pipeline] Information: "component "OLE DB Destination" (304)" wrote 772 rows.

Everything is GREEN in SSIS both under Control Flow & Data Flow (shows 17,622 rows going into the OLEDB Destination module), but I am not getting all rows into my SQL table?

Any ideas or hints as to what I am doing wrong?

PGarberick SSCrazy Points: 2098 More actions · Answer 8

Thanks Padraig, it works in sql 2010 as well. Saved my tookus it did, yesss.