0

I'm currently setting up a new server on which to run SSIS jobs in the future, the data source is an Oracle database.

This is my set up:

  • SQL Server 2019
  • Integration Services 2019
  • Oracle Client 12c 64bit Version

For development I use Visual Studio 2019 and I develop and debug on the same machine where the jobs are to run.

In the project settings in Visual Studio the usage of a 64bit environment is set to True.

We have installed the Oracle client components in 64bit to use Oracle sources in a 64bit runtime environment. This works fine for an ODBC connection, but not at all for an OLE DB connection. Unfortunately, when setting up the OLE DB Connection Manager with MS Provider for Oracle, I am told that the Oracle client connection cannot be found and that they would have to be installed first. This works fine for an ODBC connection, but not at all for an OLE DB connection.Unfortunately, when setting up the OLE DB Connection Manager with MS Provider for Oracle, I am told that the Oracle client connection cannot be found and that they would have to be installed first. Unfortunately, when setting up the OLE DB Connection Manager with MS Provider for Oracle, I am told that the Oracle client connection cannot be found and that they would have to be installed first. When I then try to use the OracleClient Data Provider, I am told that only the 32bit version is installed, which is totally contradictory. The error is: The attempt to load Oracle client libraries has thrown a BadImageFormatException.

A look into the registry showed that the Oracle drivers are stored under system32, despite the 64bit installer.

A 32bit client was previously installed, but was uninstalled and the registry was cleaned up before the new installation.

I'm totally wondering why the installation leads to such a behavior and what I have to do to get an OLE DB connection to Oracle in 64bit mode.

I hope that someone can help me here.

ReFab
  • 31
  • 4
  • 2
    Unless it's changed in SSIS 2019 (and I don't recall reading anything to suggest it has) Visual Studio is *still* only a 32bit application, meaning it can only operate in a 32bit manner. If you are running SSIS packages in Visual Studio then you need to use 32bit drivers, as Visual Studio can't reference 64bit ones. – Thom A Dec 01 '20 at 14:22
  • The (deprecated) Microsoft OLE DB Provider exists only for 32-bit. Install the OLE DB Driver from Oracle, which is available for 32-bit and 64-bit. Have a look at https://stackoverflow.com/questions/34803106/how-to-connect-to-oracle-11-database-from-net/34805999#34805999 in order to get a better overview. – Wernfried Domscheit Dec 01 '20 at 15:37
  • If you like to install the 32-bit **and** 64-bit Oracle client, follow this instruction: https://stackoverflow.com/questions/24104210/badimageformatexception-this-will-occur-when-running-in-64-bit-mode-with-the-32#24120100 – Wernfried Domscheit Dec 01 '20 at 15:39
  • @Larnu, that's wrong, Visual Studio can run and load 64-bit assemblies. However, you cannot modify your code while debugging in 64-bit mode (at least not in VS2012, perhaps it is possible in newer version) – Wernfried Domscheit Dec 01 '20 at 15:41
  • Yes, `%windir%\System32` directory is reserved for **64-bit** applications, see [File System Redirector](https://learn.microsoft.com/en-us/windows/win32/winprog64/file-system-redirector?redirectedfrom=MSDN) - don't be confused about the name! – Wernfried Domscheit Dec 01 '20 at 15:48
  • Hey Wernfried, many thanks for your explanations. Now it works! – ReFab Dec 01 '20 at 16:18
  • Glad Wernfried's comments helped; if he wants to consolidate it into an answer, be sure to mark it as accepted, so people know your question is answered. If he doesn't create an answer, after several days, you can add an answer to your question and then mark it as accepted. – Mark Stewart Dec 01 '20 at 18:41
  • 1
    @MarkStewart, I liked an answer from me which already received 55 upvotes - it does not make any sense to copy/paste it again, it would be the fourth copy meanwhile. – Wernfried Domscheit Dec 01 '20 at 18:58
  • Ah, I didn't read your linked answer! Sorry. – Mark Stewart Dec 01 '20 at 19:00

0 Answers0