2

Despite that OLE DB and ADO.NET are newer than ODBC potentially, I've heard that ODBC connections to Oracle are potentially faster within SSIS.

I'm trying to set up a simple ODBC connection to an Oracle DB in SSIS

The OLE DB / ADO.Net connections were easy. I entered the credentials and boom, connected.

ODBC proves very difficult. I'm running Windows 10 64 bit. Visual Studio 2015 (I believe the designer for SSIS was only meant to initially play with 32-bit ODBC connections). Oracle is version 12c.

So ... I did the convoluted Oracle driver install. Downloaded Oracle basic light express + ODBC. Put them in the same directory. Ran odbc.install.exe WITH admin privileges. Finally got the Oracle driver to show up in Microsoft ODBC (32 or 64, ended up downloading both eventually). I test the connection in Microsoft ODBC setup? Success!! NOOOOW. .. enter Visual studio. Set up connection --- test --- FAIL! Some cryptic error .... 127 ... the "oracle" in quora32.dll can't be found.

Are Microsoft Drivers for Oracle still available for download?

TallTed
  • 9,069
  • 2
  • 22
  • 37
user45867
  • 887
  • 2
  • 17
  • 30
  • if you test it with ODBC Data sources 32-bit from windows it works? – Yahfoufi Mar 06 '19 at 08:02
  • I tried to search for this issue, the weird thing is that there is not available documentations or article on Oracle ODBC since all available article are on OLEDB and attunity. I don't think you will get better answers but I Hope you will :) – Yahfoufi Mar 11 '19 at 11:31

2 Answers2

0

32-bit vs 64-bit

I think that the main cause of the problem is that Visual studio is a 32-bit application and the ODBC driver installed is 64-bit. On the other hand the operating system is 64-bit then it will work normally. Check the following links for more information:

Attunity connectors

I really didn't tried connecting to Oracle using ODBC, but if your goal is to achieve higher performance then the fastest way to connect to Oracle from SSIS is using Attunity high speed connectors for Oracle that have been selected by Microsoft to be included with SQL Server Integration Services (SSIS).

Check the following links for more information:

Hadi
  • 36,233
  • 13
  • 65
  • 124
  • I'm trying to use ODBC to bypass Attunity since good word on the street is that most of the Attunity advantages are merely because it's ODBC. I guess they "figured out" how to connect using the dayum thing. I would use them but - they require Enterprise Edition of SQL Server (a cool $13k per license). I emailed them how much their stand-alone connector would be. They said they'd reply in 24 hours. They never did. Great company I guess. Anyway, if their advantage is mostly ODBC, thought it would be great to figure it out - but between Microsoft and Oracle, Jebus Krist almighty. – user45867 Mar 05 '19 at 22:10
  • @user45867 i think it is a 64-bit vs 32-bit issue, check my answer update – Hadi Mar 05 '19 at 22:59
  • Visual studio is 32 bit. My OS is 64 bit. BUT I tried both the 32 bit and 64 bit drivers from oracle. I have a feeling the problem is pointing towards the right Oracle Home (the ODBC driver can only be installed with a new Oracle Home setup and client install, dumb if you ask me, but much software choices are). THEN there are some things that simply don't work in VS 2017 vs VS 2015 (Native OLE DB for Oracle for instance) and perhaps certain ODBC connections are no exception, except they don't work in 2015. An proper "install" of the driver, and/ or maybe a "signing" process – user45867 Mar 06 '19 at 17:05
  • May be necessary as well, as it is with OLEDB.dll files. Don't ask me exactly what this signing does or why it's necessary. Anyway my point is, the complication and lack of documentation is vast. It will ensure IT consultation jobs for decades to come – user45867 Mar 06 '19 at 17:06
  • Attunity is not the only third party ODBC driver either. I see a couple other 3rd parties ones as well. Some are $3,000, some are $300 ... for some kind of driver. A bit ridiculous. Eh. My research will continue. – user45867 Mar 06 '19 at 17:08
  • By the way .... the 3rd party drivers (Free trial) are DIRT SIMPLE to install ... download ... in whatever directory .... click an .exe file. Boom. Done. The End. Cheapest one I found is $150 per concurrent user. For a company and the speed improvements I've noted (12x faster than OLE Db, no joke, 12-15x faster). Still for that price, might be worth trying to install the Oracle house ODBC drivers for half a day, but DAYUM they make it complicated. – user45867 Mar 06 '19 at 21:01
0

As noted in various ways on this question, the VS IDE (and its typical testing environment) is 32-bit, even when installed/running on 64-bit Windows, or when building for use/deployment on 64-bit Windows. My employer has a knowledgebase article discussing this.

Also, SQL Server, SSIS, and VS are always OLE DB or ADO.NET clients; they don't actually speak ODBC directly. When you tell either SQL Server, SSIS, or VS to connect to an ODBC driver or ODBC DSN, they invisibly use the Microsoft OLE DB [Bridge] Provider for ODBC Drivers for that connection.

The easiest way to work around all this, given that you explicitly want to use ODBC, is to install both 32-bit and 64-bit variants of all OLE DB (including the Bridge Provider) and ODBC components (and any libraries on which they depend, such as OCI or Oracle Instant Client), and to configure a pair of ODBC System DSNs (not User DSNs, for several reasons), one each 32-bit and 64-bit, which are named and configured identically excepting only the driver library.

This strategy works whether you're using ODBC drivers from my employer (which might be the "12-15x faster" ones referenced previously? if so, it's important to note that Enterprise Edition "concurrent users" is about actively connected clients, not installed seats), from another third-party, or from Oracle themselves.

TallTed
  • 9,069
  • 2
  • 22
  • 37