Oracle Install for SSIS connectivity (and drivers 32 64 bit)

Question

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.

Answer 1

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.