Oracle ODBC connection failure (using oracle's stock ODBC drivers)

Question

Want to use an Oracle-ODBC connection in Visual Studio 2017/ SSIS as it's much faster than OLE DB during tests.

Problem:

I follow Oracle's steps to the letter.

Install instant client (v18, also tried v12).

Download/ extract ODBC download in same library. Run odbc_install.exe.

See the Driver in 'ODBC Data Sources/ Admin' in Windows 10.

Add new User Data Source. TNS Service names pull up fine. Test Connection (User/ Pass) -- it works!! The Connection works!!

I tried this with 64 bit in Oracle, their instant client v18.3 or 12.2 both. All works in Window's "Oracle Source Administrator" via test connections.

I tried this with 32 bit downloads as well. All is good.

Now, open Visual Studio. First tried 64 bit (my Windows OS is 64 bit, but Visual Studio Data Tools is only 32 bit). Had a hunch it wouldn't work.

Error message "system architecture and client is not the same" or such. Gotcha.

Tried the 32 bit Oracle ODBC driver (User Source). I keep getting the same message (tried 18_3 and 12_2 versions).

Now .... SQLORA32.dll is in the very file path it named. It's right there! Why can't it be found? The test connection in ODBC Source Admin works! What is going on here?

And I'm unsure if I have to "register" something via the command line, I had to do that once before, maybe it was an unrelated issue.

To boot, when I tried a 3rd party "Devart Oracle ODBC connector" -- it's a simple 5-second install wizard that works flawlessly instantly. Problem is it's a 30-day trial and costs $150 at least. How can I can get an Oracle-created ODBC connector (Oracle being world-renowned for janky-azz products) to actually work?

Devart, and probably Attunity Oracle ODBC: 5 second installs

Oracle's own: Harder to install than breaking into Fort Knox/ learning Mandarin Chinese. Please advise.

First I recommend to remove Oracle installations entirely, see stackoverflow.com/questions/8450726/… Then, if you like to use both, the 32-bit version and the 64-bit version of Oracle, follow this instruction: stackoverflow.com/questions/24104210/… — Wernfried Domscheit
I guess I found an obscure open-source one from 2015 from some random Danish guy who lamented (among many other Oracle database bloggers) than the current Oracle ODBC market is crapola. Oracle's own ODBC drivers are shoddy hot garbage; and the commercial ones are absurd (some costing thousands of dollars). Pro tip for anyone reading this. Try EDO for open source free. Maybe Devart if you can spring the $150 per box. Otherwise stick with sluggish OLE Db connections. Or cry into your cereal. Those are your options. — user45867
Scratch that; while the EDO actually connects in Visual Studio (more than can be said for the Oracle ones) --- it doesn't actually play too well with SQL queries really. Yikes. More hot garbage. Looks like I'll probably be unloading $150. Maybe. — user45867

user45867 user45867 · Accepted Answer · 2019-03-13T20:18:22

I am answering my own question.

Unfortunately some of us ETL/ BI guys need to go so wide on problems that there's no time to figure out every little detail/ glitch of Oracle's ... whatever they're doing now.

But here's a fix. In Visual Studio 2017/ Data Tools/ the SSIS IDE .... if you want an Oracle ODBC connection (Faster than OLE Db for some reason) --- when you're setting it up, instead of selecting a NAMED "user or system data source" that you created in ODBC Source Administrator, simply using the "Builder" option (to the left of Use Connection String) for a connection string. It does the exact same steps as the ODBC Source Admin, but within Visual Studio. I don't know what the difference is here, but some wizardry/// who knows what is different, and the connection somehow, suddenly, for some reason, works.

Oracle ODBC connection failure (using oracle's stock ODBC drivers)

1 Answers