2

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.

Hadi
  • 36,233
  • 13
  • 65
  • 124
Lozik
  • 135
  • 8
  • What's the "Run64BitRuntime" option set to? Click Project -> Properties. Then at the left under "Configuration Options" click on Debugging and you'll see that option. – Tim Mylott Feb 18 '19 at 18:44
  • Forgot to mention that. I tried setting that property from false to true, but there was not change. – Lozik Feb 18 '19 at 19:26
  • It is not related to `Run64BitRuntime`, check my answer for more details – Hadi Feb 18 '19 at 20:23
  • TOAD uses only the core oci.dll but not the OLE DB driver (or any other driver as ODBC or Oracle Data Provider for .NET). TOAD is available for 32-bit and 64-bit, check which version you have installed. – Wernfried Domscheit Feb 18 '19 at 21:31

1 Answers1

1

Update 1 - Visual Studio 64-bit

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

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:

Hadi
  • 36,233
  • 13
  • 65
  • 124
  • 1
    Thank you for this post. I was able to get 32 bit reinstalled, which rectified all the issues I was having with ssis in visual studio. Good to know vs relies strictly on 32bit oracle. – Lozik Feb 18 '19 at 20:34
  • Yes, Visual Studio is 32 bit. Thus you also need a 32-bit Oracle Client - at least if you like to use built-in wizards etc. If you compile your application as 64bit then you need the 64-bit Oracle Client. – Wernfried Domscheit Feb 18 '19 at 21:30