Hello, On my dev serverI have working ssis packages that use connections Microsoft OLEDB provider for Oracle MSDAORA.1 and Oracle provider for oledb and OracleClient data provider. I use one or the other according to my needs. In anticipation and to prepare for the build of a newproduction server, I have build a test server from scratch and deployed to it the entire dev. Almost everything works except Microsoft OLEDB provider for Oracle. ssis packages on the test machine will return an error Error at Pull Calendar from One [OLE DB Source [1]]: The AcquireConnection method call to the connection manager "one.oledb" failed with error code 0xC0202009. Error at Pull Calendar from One [DTS.Pipeline]: component "OLE DB Source" (1) failed validation and returned error code 0xC020801C. [Connection manager "one.oledb"]: 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 error occurred, but error message could not be retrieved from Oracle.". I have used the same installers for OS, SQL and Oracle SQL*Net on both dev and test machines. The install and then the restore/deployment on Test went fine. Does anyone could point me to the right direction to solve this issue? Thanks, Philippe

Are you running it on a 64 bit system? Or are you using BLOBs? If answer to either of them is yes, then you have a problem. Are you using Oracle as a source or destination?

Hi, I do not use BLOBs nor 64 bits. I have a 32 Bits DELL Precision worksation that I use as test server with Windows 2003 Enterprise SP1 all patches and the RTM release of SQL 2005 dev. For Oracle Client, I use the 9.i version. I use Oracle only to read data from using select statements, I do not use linked servers. All other Oracle providers works on that machine but the Microsoft one. All databases were restored from the dev backup Before rebuilding this machine, this provider was working. After disk format and OS install, I did rebuild the machine in this order: 1) Oracle Client 2) SQL 2005 3) VS Studio 2005 4) Restore of DB's 5) Restore of sys variables and xml configurations as well as surface config 6) Restore of logins and access settings 7) VSS 2005 RTM, I take the SSIS package from there, I know it works on the other server. Thanks, Philippe

Hi,I use "Microsoft OLE DB Provider for Oracle" and it works fine.I remember having problems with the TNS-Entry in the connection managerthat didn't match with the TNSNAMES.ORADid you try the "Test connection" button in the connection config?Do you save the pwd in the connection?What is your package protection level? When you select don't save sensitive the pwd isn't saved.

Hi, I did copy over the TNSNAME.ORA that I use in the DEV Server. I made sure that the default domain is the same than it is in Devin the Oracle configuration assistant and can testthe connectionsuccessfully. I do not save the sensitive in my packages, i use config files to store the connection string including the password. This should not be relevant to this problem anyway because, if I create a new connection in the package and supply the password and then test the connection, it fails right there so the problem is at the data provider level. Thanks, Philippe

Try changing your package to not use configuration files and instead embed the information into it. Then try and see if it magically works. I've been finding that using configuration files seems a bit buggy. When they run on the server it seems like sometimes SSIS can't load them causing the package to fail. In my most recent case, I am getting the exact same error as you above and once i removed the configuration file it is now working seeming to indicate a problem loading the configuration. Since SSIS doesn't log any info that i can find about what is happening, it is virtually impossible to tell what is going on so I'm having to go with just hardcoding for now. Can't spend more time on figuring it out.

This issue is very disturbing. Yesterday, I connected again to this test server and all by magic, the Microsoft OLEDB provider was working again. I was able to create a new ssis, a new MS OLEDB For Oracle and it worked. I then tried to run one of these packages that uses config files (and that run perfectly on the dev machine). Same error than before. Then, I went back to VS Studio, creatyed a new package, created a new MS OLEDB For Oracle, and it did not work. Nor did work the new package created just before trying the other older package... I am pulling my hair on that one. The reason why I use config files is to avoid 2 major issues:1) Package creator Password expiration every 90 days that kills all the encrypted content2) Difficulties to quicklydeploy packages from machine to machine I will get a new production server real soon. I will then know if this problem is a reproductible bug or if it is specific to my newly rebuild test machine. I am not too optimistic... Regards, Philippe

I'm seeing this same problem as well. As soon as a configuration file is create the error starts. Even when modifying the .dtsconfig file to add the username & password then using dtexec to launch the package with the .dtsconfig file the error still occurs.

Could this be the problem? http://blogs.conchango.com/jamiethomson/archive/2006/09/19/OLE-DB_3A00_-Cannot-connect-using-Microsoft-OLE-DB-Provider-for-Oracle.aspx -Jamie

What helped me, too, was setting the system variable ORACLE_HOME to the oracle home directory.

hi, This article was good. Oracle connection is usedto get Data in my case. I get this error becasue package protection level was don't save sensitive the pwd. So at execution time it says null password. although it is not good practice to use protection level EncryptSensitiveWithPassword. but it works for my case.

Problme Statment: Trying to use the Microsoft OLE DB Provider MSDAORA.1 for connecting Oracle 10g databases. Connection failed because of an error in initiialising provider. oracle error occurred, but error message could not be retrieved from Oracle Error Receiving: The 'MSDAORA.1' is not registered into local machine. Problme Resolution: The problem isn't in MDAC.It is in the Oracle client install which can have an incorrect setting in registry key HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ORACLE_HOME. I checked out my registry and sure enough I had the same problem. I made the change, attempted connection again and everything worked. Problem solved!

Are you running it on a 64 bit system? Yes I recently switched over to a 64 bit system and this error came back. This time it was real easy to fix, simply set the project property Debugging Run64bitRuntime to false.Thanks/Regards, Philippe Cand

Philippe how do you set the Debugging Run64bitRuntime to false. syed I only see HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\oracle_home in my registry. Do I add an oracle_home? and if so what value. this server is 64bit

Philippe how do you set the Debugging Run64bitRuntime to false. In Development studio, right-click the solution name, choose properties, click Debugging and set run64bitRuntime to false. If you have installed both Oracle 32 and 64, you will be fine.Thanks/Regards, Philippe Cand

I get this same issue when trying to deploy my Analysis Services project. I do not see a property for run64bitRuntime in the debugging settings for AS projects. All my oracle/oracle home paths appear to be correct too (test connections are successful throughout) Anyone out there have ideas? Thanks.