December 21, 2007 at 5:42 am
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:
Date20/12/2007 12:12:18
LogJob History (CODA Database)
Step ID1
ServerSSINT12\SQLS200564
Job NameCODA Database
Step NameCODA Database Update
Duration00:00:14
Sql Severity0
Sql Message ID0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted0
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?
December 31, 2007 at 7:53 am
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
December 31, 2007 at 1:00 pm
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
January 3, 2008 at 2:53 am
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
January 5, 2008 at 11:04 am
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
August 6, 2009 at 4:05 pm
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.
August 21, 2009 at 12:52 pm
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;
February 12, 2010 at 1:45 pm
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
June 7, 2010 at 8:10 am
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?
June 7, 2010 at 8:47 am
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
August 11, 2010 at 4:23 pm
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:
October 19, 2010 at 10:32 am
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
October 27, 2010 at 4:13 am
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
October 28, 2010 at 2:55 pm
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.
June 29, 2011 at 9:23 am
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.
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply