Hi, I have installed MS SQL Server 2005 and Service Pack 2on a newWindows 32-bit environment. I also installed the 32-bit 10g client (the Administrator option). The tnsnames.ora file is configured properly and I can tnsping to the Oracle database without any issues. I have created a package in Visual Studio and it runs successfully when I execute the job manually. I saved the package to the MS SQL Server and when I log into Management Studio to create a job for this package, I receive the following error: MessageExecuted as user: xxx\SYSTEM. ...rsion 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 10:42:37 AM Error: 2007-10-10 10:42:37.28 Code: 0xC0202009 Source: x Connection manager "x.x" 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: 2007-10-10 10:42:37.28 Code: 0xC020801C Source: Data Flow Task OLE DB Source [18] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method ca... The package execution fa... The step failed. I have created the package and logged into the server with the same ID to set up the job. And I have set the ProtectionLevel property to "Don't Save Sensitive Data" since I know this has been an issue in the past when I tried automating the job. I've four posts regarding this error message on the MSDN forums that aren't helpful at this moment - I'm hoping that someone will have found a solution since those posts. Any suggestions are welcomed. Thanks for your help! Sue

Hi Sue, I have the same problem. Did you manage to fix the problem and if so how? Thanks Christxxx

Is this all the same machine, or do you run a package from aworkstation? People sometimes forget to put drivers on both the server and the machine used for developing and testing.

I get the same error when getting data from another oracle server onto a sql2005 server (different boxes) When i run the package in SSIS designer, it works fine. When i try to run it from SQL with XP_CMDShell 'dtexec /F D:\Package.dtsx' I get this error: Code Block Error: 2007-11-08 16:35:05.51 Code: 0xC0202009 Source: ... Connection manager "..." 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 Versi on 7.3.3 or later client software installation This is an excerpt from Package.dtsx: (deleted sensitive info) Code Block <DTS:ConnectionManager> <DTS:Property DTS:Name="Retain">0</DTS:Property> <DTS:Password DTS:Name="Password" Sensitive="1" Encrypted="1">...</DTS:Password> <DTS:Property DTS:Name="ConnectionString">Data Source=...;User ID=...;Provider=MSDAORA.1;Persist Security Info=True;</DTS:Property> </DTS:ConnectionManager> This MSDAORA.1provider, is that the 'Native OLEDB\Microsoft OLEDB provider for Oracle'? If it's the microsoft driver, why wouild it want the oracle components? Hope someone can help us out here, GJ

GJ,You sill need the Oracle client installed.

Gert-Jan van der Kamp wrote: This MSDAORA.1provider, is that the 'Native OLEDB\Microsoft OLEDB provider for Oracle'? If it's the microsoft driver, why wouild it want the oracle components? Hope someone can help us out here, GJ The Microsoft OLE DB provider does not implement the core Oracle network functionality - it simply provides an OLE DB interface on top of Oracle's stack. You always need the Oracle client components installed regardless of the provider you use.

I'm pretty sure i haver that installed in C:\oracle92 I'll rerun the installer, maybe that'll fix it. I'll let you know. Thanks, Gert-Jan

Gert-Jan van der Kamp wrote: I'm pretty sure i haver that installed in C:\oracle92 I'll rerun the installer, maybe that'll fix it. I'll let you know. Thanks, Gert-JanIf it's a 64-bit machine, make sure that you have the 64-bit Oracle client installed as well.

Chris,I have not solved the problem with scheduling a job to run the package created in Visual Studio. However, I was able to schedule a job that runs the import from the Oracle database and then calls the insert and update procedure. Please let me know if you want details on that - if you think it would work for you.Sue

Hi, Darren.This is all on the same machine. I am able to run the job manually from the server. But when I schedule it as a job, I am receiving errors.Sue

Hi all, I had this message as well. For me it worked under my own account, but when running the job with xp_cmdshell dtexec it failed. This was because the xp_cmdshell executes the dtexec under the SQL server account. Although it was an administrator on our box, it did not have the C:\oracle92/bin as a path variable. This is what did the trick for us: log on to the server using the same account that SQL server runs under My Computer -> Properties advanced tab -> environment variables Click new, Name=PATH, value=C:\Oralce92\bin (or wherever your oracle drivers are installed) click ok until your back at the desktop, then reboot. Took me quite a while to figure out, hope to save you all some time this way. Regards, Gert-Jan

r u using any password in that connection manager if yes try the below. Try creating a package config file and execute. OR try changing the package protection to default ie.encrypt....

Hi I have had the same issue for the past day or so, the Package runs nicely from SSIS, and when imported into SQL Server and manually executed. When Scheduled as a Job, the package fails with an Oracle Client Error. I rebooted the box (something i hadnt done after installing the Oracle client!) and now the Job excutes without errors, yay

Hi Gert-Jan is it only the issue reboot or not? I have an access to Server through remote access with network login, I am not admin right on this server. anyway, package I created under this environment and run ok under SSIS but once I put this on SQL Server Job I've got same error message as above. I have already put the path under User variables, there is already setup under System variables. but I am not able to restart the server yet. do you think this is the issue reboot the system or not? please help with this, I have spent lots of time but still finish. Cheers Neo