The OLE DB data consumer is any application or tool that utilizes OLE DB interfaces of a provider to access a broad range of data. Overview of OraOLEDB Oracle Provider for OLE DB (OraOLEDB) is an OLE DB data provider that offers high performance and efficient access to Oracle data by OLE DB. As per standard procedure we've been using for years for previous versions of both Oracle and SQL Server, I have installed Oracle's latest ODAC package, which includes the Oracle OLE driver onto our new SQL Server 2008 R2 x64 nodes. The Oracle Provider for OLE DB is also fully compatible with OLE DB.NET, however the Oracle Data Provider for.NET (ODP.NET) is the best choice for.NET developers. Because the Oracle OLE DB provider uses the latest Oracle10 g technology, Oracle OLE DB offers data access optimizations and access to Oracle-specific database features, such as.
Active6 years ago
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?
Oracle Provider For Ole Db Is Not A Valid Win32 Application
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.
Ole Db Or Odbc Error
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
JosephJoseph96555 gold badges1212 silver badges2626 bronze badges
3 Answers
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 FilesMicrosoft SQL Server100DTSBinn)
So in DEVELOPMENT (on a 64 bit machine) install both the 32 and 64 bit clients:32 bit: for development in Visual Studio IDE64 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♦
JosephJoseph96555 gold badges1212 silver badges2626 bronze badges
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.
grapefruitmoongrapefruitmoon2,86011 gold badge1717 silver badges2626 bronze badges
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.
IronRodIronRod