7

I have an SSIS package (SQL 2008) that I need to connecto to an Oracle DB (11g) with. What do I need to install to connect to oracle? What's the terminology ? All searches I've done talk about Instant Client, but on downloading that I see no exe's? I know installing the server will give me that Oracle Net manager (UI to update TNSNames.ora) but I don't want to install the entire server. That to be is overkill. What's the smallest footprint so that I can create a connection to an oracle DB via my Connection Manager in SSIS?

Also what's the difference between Instant Client & Oracle Client tools etc? There's so much arcane (atleast to me) terminology that it's confusing.

P.s. - From reading http://www.oracle.com/technetwork/topics/dotnet/index-085163.html you would think this was what I wanted, but the download just has an install.bat that seems to do nothing! Typical of the "solutions" I've tried so far.

Joseph
  • 2,155
  • 6
  • 20
  • 32

3 Answers3

11

Well, what I did was download Oracle Database 11g Release 2 Client (11.2.0.1.0) for Microsoft Windows (x64) from http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-win64soft-094461.html. It had 4 options for installing One of them being Instant Client(which did not help me). The one that works is Runtime client or something named like that. It installs Net Manager which is what I want.

PS-Adding on (as I traverse the Oracle 64 bit journey), I find that I cannot use SSIS with 64 bit oracle DB providers. I get the exceptions (on adding a connection in SSIS):

Test connection failed because of an error in initializing provider. Attempt to load Oracle client libraries threw BadImageFormatException. This problem will occur when running in 64 bit mode with the 32 bit Oracle client components installed.

I'm guessing this is because SSIS process is a 32 bit one and cannot use 64 bit oracle drivers (my host machine is Win 7 64 bit).

After testing, I found that this is indeed the case. We need the 32 bit drivers only for the SSIS IDE but can use 64 bit when running the DTSX package using the 64 bit dtexec.exe (C:\Program Files\Microsoft SQL Server\100\DTS\Binn)

So in DEVELOPMENT (on a 64 bit machine) install both the 32 and 64 bit clients: 32 bit: for development in Visual Studio IDE 64 bit: To run the DTSX package using the 64 bit version of dtexec.exe on the command line (as would be the case when we run this in a production environment)

A similar thread here.

Community
  • 1
  • 1
Joseph
  • 2,155
  • 6
  • 20
  • 32
1

I used this link Configuring SSIS 2008 to Connect to Oracle and was successful getting VS 2012 to use SSIS with Oracle 11.2 instantclient. All credit to the author, Jerry Dy. NOTE: The only registry entry required is the one for ORACLE_HOME.

AdvApp
  • 1,094
  • 1
  • 14
  • 27
1

I'm not sure of the difference between the Install Client and the Oracle Client Tools. I've always used the latter. The link below has a download for the 10g client tools (under Oracle Database 10g Client Release 2), which should be what you require to get the connectivity from SSIS.

http://www.oracle.com/technetwork/database/10204-winx64-vista-win2k8-082253.html

grapefruitmoon
  • 2,988
  • 1
  • 22
  • 26
  • how do you connect this to SSIS? after downloading i see a bunch of files including adrci.exe, genezi.exe and uidrvci.exe. not clear of how to make this driver show up in SSIS import package – Sonic Soul Mar 22 '16 at 14:38