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
Andrew Lamberth
Andrew Lamberth
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 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?
Andrew Lamberth
Andrew Lamberth
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 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
Peter Brinkhaus
Peter Brinkhaus
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2064 Visits: 7369
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
Peter Rijs
Peter Rijs
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: 1024 Visits: 602
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
wissemhabboub
wissemhabboub
Old Hand
Old Hand (310 reputation)Old Hand (310 reputation)Old Hand (310 reputation)Old Hand (310 reputation)Old Hand (310 reputation)Old Hand (310 reputation)Old Hand (310 reputation)Old Hand (310 reputation)

Group: General Forum Members
Points: 310 Visits: 53
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
rob strong
rob strong
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 32
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.
JPaulWright
JPaulWright
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: 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;
ken.trock
ken.trock
SSChasing Mays
SSChasing Mays (659 reputation)SSChasing Mays (659 reputation)SSChasing Mays (659 reputation)SSChasing Mays (659 reputation)SSChasing Mays (659 reputation)SSChasing Mays (659 reputation)SSChasing Mays (659 reputation)SSChasing Mays (659 reputation)

Group: General Forum Members
Points: 659 Visits: 1730
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
dp2010
dp2010
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 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?
ken.trock
ken.trock
SSChasing Mays
SSChasing Mays (659 reputation)SSChasing Mays (659 reputation)SSChasing Mays (659 reputation)SSChasing Mays (659 reputation)SSChasing Mays (659 reputation)SSChasing Mays (659 reputation)SSChasing Mays (659 reputation)SSChasing Mays (659 reputation)

Group: General Forum Members
Points: 659 Visits: 1730
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
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