Oracle…it’s in the name right? 🙂
Well, let me tell you…it’s nothing like that!
In order to import and process Oracle source data in SSIS you required to:
– Download several Oracle 11g Client Tools (32 and 64 bits, will be explained a few rows down)
[Connection manager “”] Error: SSIS Error Code DTS_E_OLEDB_NOPROVIDER_64BIT_ERROR. The requested OLE DB provider MSDAORA.1 is not registered — perhaps no 64-bit provider is available. Error code: 0x00000000.
An OLE DB record is available. Source: “Microsoft OLE DB Service Components” Hresult: 0x80040154 Description: “Class not registered”.
You’re simply missing the 64-bits components it seems.
After some Google-ing, I found this post here:
Microsoft OLEDB Provider for Oracle and Microsoft ODBC driver for Oracle
If you are using Oracle data sources, you should migrate to the Oracle-supplied provider and driver. Microsoft OLEDB Provider for Oracle (msdaora.dll) and Microsoft ODBC driver for Oracle (msorcl32.dll) are built by using Oracle Call Interface (OCI) version 7. Oracle no longer supports applications that use OCI version 7 calls, and these technologies are deprecated
You need to use the provider from ORACLE (32-bits), more important…the 64-bits version:
64-bit ODAC 11.2 Release 4 (220.127.116.11.0) for Windows x64
Important: It is required to install the 32-bit Oracle Developer Tools for Visual Studio download first (is required for Entity Framework design-time features). The above download fully supports Entity Framework deployment, but does not contain design-time tools.
Perform a reboot after installing both toolkits!
After the reboot, verify that the Oracle providers are listed under Linked Servers -> Providers:
Another simple trick to do is to force the package to run in 32-bits mode:
The package you currently have open will (by default) run in 64 bit mode. The setting that controls this is a property on the project called Run64BitRuntime. To access this property, right-click on the Integration Services project in your solution explorer and select Properties. Then select the Debugging node in the editor. The default here is “true”, which means all the packages in this project will run in 64-bit mode. If you change this to “false”, all the packages will be run in 32-bit mode.
Another error message I’ve received was this one (don’t know when or how):
[Connection manager “”] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x800700C1.
An OLE DB record is available. Source: “Microsoft OLE DB Service Components” Hresult: 0x800700C1 Description: “%1 is not a valid Win32 application.”.
To get this working in 64-bits mode, a suggestion would be to use the .NET providers instead:
OracleClient Data Provider