I get the following error message when I try to test my connections:

Test connection failed because of an error in initializing provider. The 'OraOLEDB.Oracle.1' provider is not registered on the local machine

I originally had 32 bit and 64 bit version of Oracle 12 client installed on my work machine. I uninstalled the 32bit version, because it was causing some conflicts with another application, but now after uninstalling I can no longer use my oracle connections in SSIS.

I'm using Visual Studio 2017 - I am pretty sure I installed 64bit version of SSDT, but it looks like they install under the 86x programs folder. I can still connect via TOAD, so it looks to be an issue of SSIS not pointing to the 64bit installation, I think. Can I manually set SSIS to point to the 64bit version, or do I need to reinstall 32bit?

TSN_Admin is set to the oracle12c_64 tsnname.ora file. I have also tried using the regsvr32.exe on oraoledb12.dll file, but I get an error message, even after running command prompt as admin:

The Module "OraOLEDB.dll" was loaded by the call to DllRegisterServer failed with error code 0x80070005.

I can reinstall the the 32bit version, but I need to wait for my company's IT department to approve the request first. It could be days before I get a response.

Update 1 - Visual Studio 64-bit

After searching it looks like there is not Visual Studio 64-bit version:

• Visual Studio 64 bit?

Initial Answer

I don't know if there is an official documentation for this, but based on my own experience you need to instal oracle client 32-bit on the developpement machine, because SQL Server Data Tools for Visual studio is a 32-bit application and it will not work will work with oracle client 64-bit.

But you can execute .dtsx package without any issue if you are working with oracle 64-bit client, you need to use the appropriate DtExec path:

32-bit

C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DtExec.exe

64-bit

C:\Program Files\Microsoft SQL Server\100\DTS\Binn\DtExec.exe

While searching, i found many other links that share the same opinion:

• Oracle Install for SSIS connectivity (and drivers 32 64 bit)
• Oracle Client Provider for OLE DB in SSIS not working
• Connecting to Oracle 11g on 64-bit (x64) machine SSIS 2012