SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER


SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER

Author
Message
Marie Kelly
Marie Kelly
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 40
Many thanks for everyone's contribution to this thread. I finally have got an SSIS package to execute, thanks to the responses above regarding the 32 bit execution and its options at runtime. I also agree: there is a working 64 bit Oracle client for Windows:

Oracle Data Access Components for Oracle Client 11.1.0.7.10 Beta
http://www.oracle.com/technology/software/tech/windows/odpnet/index1110710beta.html

I did install this on my Windows 7 workstation, a 64-bit host, and have finally connected to the Oracle server, also a 64-bit UNIX server, but as others have noted, after much research and error resolution. The first errors were very vague, complaining about name resolution of the Oracle server. May I suggest also adding a system variable TNS_ADMIN to the [client] operating system? After that, I was at least able to connect to Oracle from the Windows host, of course leading to the package execution issues others on this thread kindly helped to resolve.

Here is the detail on the system variable needed after installation of the Oracle client:
Add an entry to the System Variables of Environment Variables, in Advanced tab of the System Properties in Windows.
VARIABLE = "TNS_ADMIN"
VALUE = "C:\app\mkelly\product\11.1.0\client_1\Network\Admin"
This path has a folder called "Admin" and that subfolder has the critical connection file "TNSNAMES.ORA", with the connection strings to all Oracle environments.

I am pretty sure I needed to reboot the machine before the new variable is recognized by the operating system.

Here is a reference on the use of system variables that help locate the Oracle client:
http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.websphere.ii.foundation.conn.fw.orc.doc/configuring/iiylsorcenvvars.html
josp
josp
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 18
I installed microsoft updates and started getting this error. My fix in SQL Server was in SSIS connection manager under "server name" specify the port number e.g. change "AK11\PROD" to "AK11\PROD,52" if your database uses that port.

Also told it to use 32bit (since other posts say that) under Project\Properties\Debugging\Run64bitRunTime to False
Padraig FItzgerald
Padraig FItzgerald
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 99
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
tmitchelar
tmitchelar
SSC Veteran
SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)

Group: General Forum Members
Points: 249 Visits: 508
Installing both 32 bit and 64 bit drivers/providers on the server has helped resolve this issue in the past at clients. (BIDS uses 32 bit, but Job Agent uses 64 bit). There are a few articles floating around online regarding this issue.
4x4krzy
4x4krzy
Valued Member
Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)

Group: General Forum Members
Points: 65 Visits: 259
I read all of this thread and it does not pertain to my situation. Any help would be great!
I am getting the same error "The AcquireConnection method call to the connection manager failed with error code 0xC0202009.", here is the situation.

-I have a SSIS server that stores all the SSIS packages and runs them.
-My production box has the databases and flat files that are to be decrypted and imported to a database.
- Both SQL servers are 64 bit SQL 2008 R2 running on Windows 2008 R2

From the SSIS server the package works just fine running in Visual Studio. I am logged onto the box as the service account so the package is created by the same account that runs it. The production box also has the SSIS service account on it too.

However, when I schedule the SSIS package on the SSIS server to run I get the error "The AcquireConnection method call to the connection manager failed with error code 0xC0202009."

If I scheduled it on the production box and it runs fine. The package will not run on the SSIS SQL box at all unless in debug mode in Visual Studio.

In the package properties window there is a ForcedExecutionValueType set to int32 and ForcedExecutionValue set to False. This is on both the new and old SSIS servers.

I do have the same SSIS server set up already and it works. The only difference is the SSIS server is SQL 2008 not R2

I am at a loss of why this is not working.
Marie Kelly
Marie Kelly
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 40
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 "DTSTongueassword"' 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
4x4krzy
Valued Member
Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)

Group: General Forum Members
Points: 65 Visits: 259
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
sudhakara
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1034 Visits: 1386
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
santhosh_k_gadepally
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 41
Thanks a lot Padraig, it working for meSmile
singhster
singhster
SSC-Addicted
SSC-Addicted (488 reputation)SSC-Addicted (488 reputation)SSC-Addicted (488 reputation)SSC-Addicted (488 reputation)SSC-Addicted (488 reputation)SSC-Addicted (488 reputation)SSC-Addicted (488 reputation)SSC-Addicted (488 reputation)

Group: General Forum Members
Points: 488 Visits: 344
Thanks Padraig, that worked for me too.



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search