Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER Expand / Collapse
Author
Message
Posted Wednesday, August 11, 2010 4:23 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 14, 2013 12:44 PM
Points: 2, 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

Post #967865
Posted Tuesday, October 19, 2010 10:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 20, 2012 3:26 PM
Points: 7, 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
Post #1007133
Posted Wednesday, October 27, 2010 4:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 28, 2014 10:41 AM
Points: 9, Visits: 68
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
Post #1011373
Posted Thursday, October 28, 2010 2:55 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 10, 2014 2:17 PM
Points: 124, Visits: 485
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.
Post #1012663
Posted Wednesday, June 29, 2011 9:23 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, April 14, 2014 8:54 AM
Points: 32, Visits: 186
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.
Post #1133822
Posted Wednesday, June 29, 2011 11:03 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 14, 2013 12:44 PM
Points: 2, 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 "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.
Post #1133918
Posted Wednesday, June 29, 2011 11:13 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, April 14, 2014 8:54 AM
Points: 32, Visits: 186
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
Post #1133925
Posted Saturday, July 21, 2012 11:52 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 1:17 PM
Points: 603, Visits: 1,280
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.
Post #1333379
Posted Friday, December 07, 2012 7:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 01, 2013 10:51 AM
Points: 1, Visits: 41
Thanks a lot Padraig, it working for me:)


Post #1394055
Posted Friday, February 22, 2013 2:37 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 7:28 AM
Points: 449, Visits: 290
Thanks Padraig, that worked for me too.


Post #1422933
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse