SQL 2005 SSIS Package pulling data from Oracle 10 db. I recieve the following error
If i run the packages manually in the development studio, i do not recieve this error, but when schedule in a SQL job i recieve this error. The following i have verified: The account running SQL Job Agent is a memebr of the Local Admin group and the local admins group has access to all the folders. 06/22/2010 03:00:00,DS_EIX_UPCaseCode,Error,1,LANDINI,DS_EIX_UPCaseCode,Load EIX UPC Case Code,,Executed as user: CBF\sql2000admin. ...n 9.00.4035.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 3:00:00 AM Error: 2010-06-22 03:00:02.73 Code: 0xC0202009 Source: EIX_UPCCaseCode Connection manager "DW1.bef_apps" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80004005 Description: "Oracle client and networking components were not found. These components are supplied by Oracle Corporation and are part of the Oracle Version 7.3.3 or later client software installation. Provider is unable to function until these components are installed.". End Error Error: 2010-06-22 03:00:02.73 Code: 0xC020801C Source: Data Flow Task OLE DB Source  Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method ... The package execution fa... The step failed.,00:00:03,0,0,,,,0 Lone Coder
June 22nd, 2010 7:02pm
When you run this package in Development Studio - where are you running it? On your computer? Or are you RDP'd to the server desktop? Talk to me now on
June 22nd, 2010 9:52pm
The account running the job should have sysadmin rights/role from the DB side.
June 22nd, 2010 10:21pm
The error message indicated that the problem was related to Microsoft OLE DB Provider for Oracle which is old and only supports Oracle 8.0 or prior versions. I recommend that you use Oracle's OLE DB Provider which can be downloaded from Oracle's website. In addition, please aware 32-bit and 64-bit driver issues if you are running SSIS packages on a 64-bit environment. By default when you use BIDS to run your SSIS package, it loads 32-bit Oracle client since BIDS is a 32-bit application. But if your SQL Server is 64-bit, by default it will loads 64-bit Oracle client when executes your SSIS package. If this is your situation, please also install 64-bit Oracle client on your OS or you can refer to these two FAQs to run your SSIS package with 32-bit runtime in your SQL Agent job: http://social.technet.microsoft.com/Forums/en-US/sqlintegrationservices/thread/e13c137c-1535-4475-8c2f-c7e6e7d125fc http://social.technet.microsoft.com/Forums/en-US/sqlintegrationservices/thread/d85b134b-17c1-4353-be0e-60cc9585053e Please remember to mark the replies as answers if they help and unmark them if they provide no help
June 23rd, 2010 7:01am
Ok.. We have loaded the Oracle 10 client tools on the server. And the account runnning the SQL Job Agent is a sys admin in the database. When i ran the development i was rdc in with the SQL job account... Thanks for the help. SalLone Coder
June 23rd, 2010 9:28pm
hello I'm having the same problem as salsack above. i have installed the client on the server and i am executing the job from a sql agent job from dtexec command line. can anyone help?
February 26th, 2011 4:03am