latest SQL Server 2019 SSIS oracle client libraries threw BadImageFormatException

Question

Hi, I installed the latest of everything, Visual Studio Community 2019, Oracle 19c, the latest SSIS visx for Visual Studio 2019, and the latest Oracle Managed Data Access. When I run c#, I nuget the Oracle Managed Data Access, set the c# console app Build target for x64 and everything runs great. When I try to access Oracle with SSIS I get a "attempt to load oracle client libraries threw BadImageFormatException". I'm using an ADO.Net task for Oracle access. The OLEDB task doesn't show an Oracle driver. I think I could set the runtime ssis to be 32 bit, but I need to get past the ssis client development to get there. I'm pretty sure the Oracle Managed Data Access I downloaded has 64 bit. In c# it shows the version for Oracle Managed Data Access as 4.122.19.1 . Any thoughts on how to shake this loose? We've been fighting this 64 bit/32 bit thing for 20+ years. Aren't we ever going to get past it, or at least move on to 64bit/128 bit?

Answer 1

Ok, got Oracle db re-installed and I am making some progress. In Server Explorer of ssis I was able to get ssis to connect to my local Oracle as a DataConnection. I can see Oracle tables in Server Explorer. I selected the ODP.Net Managed Driver to make the connnection. I still don't see an Oracle Source like I can see at work in VS 2017 Enterprise. I still can't get Ado.Net or OLEDB tasks to connect to Oracle with any of the selectable drivers including Microsoft OLEDB Driver for Oracle. So is there some way I can check inside of SSIS that I have Microsoft Oracle Connector actually installed? Like is there an Oracle driver that only shows up when you have Microsoft Oracle Connector installed? The comment below is the answer.

Answer 2

Hi @Flash Gordon ,

May I know if you download the Microsoft Connector for Oracle ?

If not, please download it.

We can refer to the following links:

1. Microsoft Connector for Oracle
2. Oracle source

Best Regards,
Mona

----------

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues in October--Users always get connection timeout problem when using multi subnet AG via listener. Especially after failover to another subnet.

Answer 3

Mona, Thanks, I tried to install the Microsoft connector for oracle. Kind of wierd because the 5.0 version has an earlier version date than the 1.0 version data but I think I installed it. Now it's asking for the oracle client and i'm working on that.

Answer 4

Just an update on where this is. I installed the oracle client and downloaded the Microsoft connector for Oracle and my locally installed oracle database just blew up for some reason. sqlplus stopped working, sqldeveloper stopped working, c# reading oracle stopped working. I think maybe with Oracle 19c db installed on a win 10 machine, you're not supposed to install the Oracle client. After you install Oracle 19c db only on a laptop, a lot of Oracle things are there: sqlplus is there, localhost:1521/orcl just works, just orcl as tnsnames seems to work even though I can't find tnsnames.ora. There is no uninstall for Oracle 19c so I had to remove it manually. Then I re-installed Oracle 19c and I got back to where I was including c# reading oracle. I don't need ssis reading oracle that bad so my next step here is going to be a cautious one. I don't have VS 2019 Enterprise so I can't try any of the direct Microsoft Oracle tasks. There are rumors we may get VS 2019 Enterprise (from VS 2017 Enterprise) at work so maybe I'll try it there.

Answer 5

Production server:
(1) MS SQL Server Enterprise Edition, SSIS run-time environment on the server.
(2) MS Oracle Connector.

Dev. machine:
(1) VS 2019 any edition. Visual Studio 2019 Community Edition should be fine,
(2) SQL Server Integration Services Projects extension for VS2019.
(3) MS Oracle Connector.