SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Business Intelligence » SSIS Error Code...

21 posts, Page 1 of 3

1 2 3 »»»

SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER

Rate Topic

Display Mode

Topic Options

Author

Message

Andrew Lamberth

Posted Friday, December 21, 2007 5:42 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, June 15, 2011 10:37 AM
Points: 8, Visits: 53

I have created an SSIS package to copy the data from an Oracle database to SQL Server 2005. the package runs sucessfully from VS. When I try yo run the packge from the SQL Agent it starts successfully, but then fails when trying to copy the data from the Oracle database into my SQL Server staging table. The full error I receive is:

Date 20/12/2007 12:12:18
Log Job History (CODA Database)

Step ID 1
Server SSINT12\SQLS200564
Job Name CODA Database
Step Name CODA Database Update
Duration 00:00:14
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
Executed as user: SSIDEV\SqlServer. ...n 9.00.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 12:12:18 Error: 2007-12-20 12:12:32.00 Code: 0xC0202009 Source: OAS_BALANCE Connection manager "SourceConnectionOLEDB" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040154. An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered". End Error Error: 2007-12-20 12:12:32.01 Code: 0xC020801C Source: Data Flow Task Source - Query [1] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "SourceConnectionOLEDB" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. End Error Error: 2007-12-... The package execution fa... The step failed.

I am using the Oracle 10.2.0.3 OLEDB driver for the source connection.

I have also saved the package in SQL Server using the Encrypt Sensitive with Password option.

Any ideas?

Post #435591

Andrew Lamberth

Posted Monday, December 31, 2007 7:53 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, June 15, 2011 10:37 AM
Points: 8, Visits: 53

The resolution to the problem is to set-up the SQL Agent job step as a Operating system (CmdExec) and in the command line use a statement similar to the one below:

C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe /f "f:\bi\64bit\DUMP\DUMP_DATABASE\DUMP Database.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E /DECRYPT xyz123

The 'xyz123' after the /DECRYPT clause is the password used when saving any sensistive data in the package e.g. database passwords.

there doesn't appear to be any explanation on MSDN as to why you cannot use a Job Step type of: SQL Server Integration Services Package
to execute the package. This only appears to be an issue with SQL Server 2005 64bit

Post #437569

Peter Brinkhaus

Posted Monday, December 31, 2007 1:00 PM

SSCommitted

Group: General Forum Members
Last Login: Monday, May 20, 2013 11:28 PM
Points: 1,561, Visits: 6,105

As far as I can remember, there's no 64-bit provider for Oracle. Running the package in a 32-bit environment, as you did, solves the problem.

Peter

Post #437652

Peter Rijs

Posted Thursday, January 03, 2008 2:53 AM

SSC Eights!

Group: General Forum Members
Last Login: Thursday, April 25, 2013 6:03 AM
Points: 856, Visits: 573

Actually, there is a 64 bit Oracle provider available from Oracle (OraOLEDB.Oracle.1, see the Connectivity whitepaper).
When implementing ETL packages at a customer however, we found that no performance benefit from using the 64 bit OraOLEDB over 32 bit MSDAORA, so we stuck to MSDAORA, and scheduled our packages to run with the 32 bit DTEXEC instead of the 64 bit.
Once we had all data inside SQL 2005, we ran all other packages on 64 bit however, with considerable performancegains over running 32 bit!

Peter Rijs
BI Consultant, The Netherlands
MCITP BI Dev & DB Dev (SQL 2008 & 2005)

Post #438293

wissemhabboub

Posted Saturday, January 05, 2008 11:04 AM

SSC Veteran

Group: General Forum Members
Last Login: Sunday, March 01, 2009 7:19 AM
Points: 272, Visits: 49

hi
verify if SQL server agent has the autorization to access of oracle database or create an configuration file and use it with SQL server Agent

Post #439265

rob strong

Posted Thursday, August 06, 2009 4:05 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, February 08, 2012 10:24 AM
Points: 1, Visits: 30

I have run error while trying to import from Access Database. The weird part is that the error only occurs when the package is called from a SQL Job. I can run it all da long and have no problem on Server Manager but when a job calls it BAM it throws the error? Any ideas are much appreciated.

Post #766606

JPaulWright

Posted Friday, August 21, 2009 12:52 PM

Forum Newbie

Group: General Forum Members
Last Login: Friday, August 21, 2009 12:49 PM
Points: 1, Visits: 0

When using Microsoft’s Visual Studio (aka: Business Intelligence Development Studio) to create an SSIS package that has a connection to Oracle and the SSIS package deployment shows:

Error : AcquireConnection call to the connection manager . failed with error code 0xC0202009.

How can this be fixed?

Solution 1: Make sure the provider being used is installed on both the development and deployment machines. This may require Oracle client to be on the SQL Server machine. To see the providers that SQL Server has available, open MS SQL Server Managaement Studio, then connect to the Database Engine. See Server Objects, Linked Servers then Providers. To see the providers on a development PC with XP, right click and create a text file on the desktop. Rename the text file MyProviders.UDL. Double-click, or right-click Open, the text file and see the “Data Link Properties” form with the tab called providers. Test a connection, then close the form, then open the UDL file (using right-click open with) using Notepad. The connection string will be seen.
Solution 2: Use an XML configuration file to change the provider when deployed. To create a configuration file in Visual Studio, open the SSIS solution then right-click on the background of the Designer window and pick the Package Configuration Organizer. Make sure your configuration file is in a folder that SQL Server can see it, and that the file contains the connection string and password parameters. Change the connection string so that it has the correct provider. For Oracle it might be one of these:
Data Source=ORACLE1;User ID=ORACLE_USER;Provider=MSDAORA.1;Persist Security Info=True;
Data Source=ORACLE1;User ID=ORACLE_USER;Provider= OraOLEDB.Oracle.1;Persist Security Info=False;

Post #775347

ken.trock

Posted Friday, February 12, 2010 1:45 PM

Old Hand

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 2:51 PM
Points: 315, Visits: 1,356

A year or 2 ago we though we had this very issue licked. The other night, out of the blue, our Oracle --> SQL Server job stops working with this "Cannot acquire connection from connection manager" error. What worked for us was to set DelayValidation = True for all datapump tasks sourced by Oracle and to redeploy the package. Our SQL environment is Win 2003 Server on an i64 using the Oracle 9.2 client tools to pull from a number of Oracle boxes on 10.2.0.3.0 of the DB.

Ken

Post #864933

dp2010

Posted Monday, June 07, 2010 8:10 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, June 07, 2010 1:20 PM
Points: 1, Visits: 3

I have the same problem but I don't have the 32 bit SQL Server installed and the system admins will likely not agree to installing it. Is there any other workaround to this problem?

Post #933528

ken.trock

Posted Monday, June 07, 2010 8:47 AM

Old Hand

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 2:51 PM
Points: 315, Visits: 1,356

So your development and deployment environments are both x64 or i64. You're using the command line syntax to access a 32bit version of the Oracle driver because there is no 64bit oracle driver. Actually there is a 64bit oracle driver and we're TRYING to get it working. If successful our SQL job could simply call our package as a package, not as command line, imagine that!

Anyhow, I was shown this handy link of testing for availability of 32bit drivers on a 64bit machine.

http://blogs.msdn.com/farukcelik/archive/2007/12/31/udl-test-on-a-64-bit-machine.aspx

Hope this helps,
Ken

Post #933549

« Prev Topic | Next Topic »

21 posts, Page 1 of 3

1 2 3 »»»

Permissions