0

I 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).

ERROR[IM003]

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.

Improve this question

1 Answer 1

Reset to default
2

I have not used Visual Studion in the last 7 years and I have never ever used SSIS, but this is your 2nd frustrated post about this in 2 weeks, so here we go:

Oracle ODBC driver installation BEGIN

Download 32-bit 18.3 Oracle instant client (instantclient-basic-nt-18.3.0.0.0dbru.zip) + ODBC (instantclient-odbc-nt-18.3.0.0.0dbru.zip), extract it (C:\instantclient_18_3_32).

Actual ODBC driver installation, takes about 1 second:

C:\instantclient_18_3_32>odbc_install.exe
Oracle ODBC Driver is installed successfully.

C:\instantclient_18_3_32>

Set up tnsnames.ora in C:\instantclient_18_3_32\network\admin, set TNS_ADMIN to this directory in system variables, create DSN with C:\Windows\SysWOW64\odbcad32.exe, test it.

Oracle ODBC driver installation END

Create a new BI/Integration Services project, add a new ODBC connection, select DSN from list, enter user + password, test:

enter image description here

There we are. Close VS, add C:\instantclient_18_3_32 to PATH in System Variables.

Repeat the test, and:

enter image description here

Guess it's time to start learning Mandarin Chinese.

Improve this answer
5
  • Eh. I did all that to the letter. You could have just said make sure the path variable lists the oracle directory correctly. Tried that, and Oracle claims its "instant client" doesn't need to mess with Path for things like ODBC connections. Did it anyway. Made it top of the list. Got rid of any references to other oracle installations (admittedly, another oracle install might be causing problems). Are you using VS 2015 or VS 2017? ---- It seems I got 2015 working, but now it's complaining about Attunity connectors --- thought those were sorta 3rd party ... sigh. The Devart connector just works
    – user45867
    Mar 13, 2019 at 22:46
  • I'm utterly baffled how 'Test Connection' always works for everything in ODBC Source Administrator ... but then fails in Visual Studio. I know some credentials are "cached" as well like where to look for Oracle home and so forth even regardless of environmental variables. No idea whoever made this so complicated.
    – user45867
    Mar 13, 2019 at 22:49
  • It actually might be a "security/ admin" thing ... as ODBC is always run under admin credentials somewhat, but visual studio isn't. But should that matter? Maybe that's why ... TNS_Admin is actually pointing to a (correct) network file with the proper definitions, but my local_admin account can't see ... maybe the whole thing is running Visual Studio as an admin? Seems pointless but eh
    – user45867
    Mar 13, 2019 at 22:52
  • For anyone else reading: Use ADO.Net. The key is 'fetchsize' parameter. That solves all. Forget Oracle's ODBC or the commercial ones. It's a black hole of despair.
    – user45867
    Mar 14, 2019 at 15:53
  • There is no common way how different drivers try to find the tnsnames.ora file, see stackoverflow.com/questions/28280883/…
    – Wernfried Domscheit
    Mar 14, 2019 at 19:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.