Trying to Connect SSIS to Oracle on x64

#41

DM,

I believe that Joseph D. Wagner resolved his problem by following the
instructions in the link posted by matteus.

From MsTechnet:
Integration Services Considerations on 64-bit Computers
http://technet.microsoft.com/en-us/l.../ms141766.aspx

These instructions are about how to run an SSIS package as a 32-bit package
from a 64-bit server. That 32-bit package will then be able to use the
32-bit Oracle drivers. Joseph got his package to run.

RLF

"DM" <DM (AT) discussions (DOT) microsoft.com> wrote

Quote:

somebody found a solution for this issue?

regards,
DM

"Joseph D. Wagner" wrote:

matteus wrote:
On Jul 3, 10:50 am, "Joseph D. Wagner" <the... (AT) josephdwagner (DOT) info
wrote:
Oh great magi of Microsoft and Oracle, please help me!

I'm trying to get SSIS to connect to Oracle on 64-bit, but I'm having
trouble.

It's running Windows Server 2003 R2 (x64), SQL Server 2005 SP2 (x64),
and the full Oracle Client 11g1 (x64).

The Oracle client can connect to the server and the connection tests
ok.
I can even pull data from SQL Lite. However, I'm having trouble
configuring SSIS to connect to Oracle so it can load the data in SQL
Server.

Below are the error messages, and after them, I'll give you a bunch of
the things I've tried. Please don't overlook the little things. My
actual experience with Oracle is non-existent. I may have easily
missed
something simple.

----------------------------------------------------------------------------
ERROR FROM MICROSOFT DRIVER:
Oracle client and networking components were not found. These
components
are supplied by Oracle Corporation and are part of the Oracle version
7.3.3 or later client software installation.

Provider is unable to function until these components are installed.
----------------------------------------------------------------------------
ERROR FROM ORACLE DRIVER:
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.
----------------------------------------------------------------------------
CAN'T REMEMBER WHICH DRIVER:
The 'OraOLEDB.Oracle.1' provider is not registered on the local
machine.
----------------------------------------------------------------------------
CAN'T REMEMBER WHICH DRIVER:
The specified module could not be found. (Exception from HRESULT:
0x8007007E)
----------------------------------------------------------------------------

THINGS I'VE TRIED:
Completely removed all previous Oracle installations and registry
settings.

Reinstalled the full Oracle client x64. It's installed to
D:\Oracle\products\11.1\client\ so this directly contains bin. D: is
a
local drive, not networked.

Completely uninstalled and reinstalled the Microsoft .NET Framework
versions 2.0 SP1 and 3.0 to ensure that the x64 versions were
installed.

Ensured that ORACLE_HOME was set to the above directory in the
Environmental Variables.

Ensured that both ORACLE_HOME\bin and ORACLE_HOME\lib were in the path
ahead of everything else.

Ensured that ORACLE_HOME, ORACLE_HOME_NAME, ORACLE_GROUP_NAME, and
NLS_LANG were set in HKLM\Software\Oracle. Inst_loc is set to
C:\Program Files\Oracle\Inventory.

Set LOCAL in both the Environmental Variables and Registry.

Re-verified connectivity with the Test Connection button in Oracle
configuration and by pulling data through SQL Lite.

The only thing I haven't done that I can think of is use a Connection
String on Microsoft's ODBC drivers. I was planning on trying that
tomorrow.

MY ANALSIS/THOUGHTS:
It's as if the Microsoft drivers don't know Oracle is installed, but I
don't know how to tell them it's installed. The only copy of
ociw32.dll
is in ORACLE_HOME\bin.

I have no idea how the Oracle drivers are coming up with a 64/32 bit
mismatch considering that I personally removed and reinstalled the x64
client. (I didn't try uninstalling/reinstalling MS XML Parsing
services. Would that make a difference?)

I'm hoping there's somebody out there with a miracle answer for me,
but
any help at all would be greatly appreciated.

Thanks in advance.

Joseph D. Wagner

From MsTechnet:
Integration Services Considerations on 64-bit Computers
http://technet.microsoft.com/en-us/l.../ms141766.aspx

let us know
M.

I removed the x64 Oracle client and replaced it with the x86 Oracle
client. That did the trick. It's now connecting and pulling data.

I did some more searching along these lines. It turns out that Visual
Studio 2005 that came with SQL Server x64 is in fact an x86 (32-bit)
version. It does not appear to be x64 aware and hence could not see the
x64 Oracle client. I am not fully convinced this is Microsoft's fault.
I'm going to reserve judgement until after I see what happens with
IBM's DB2 drivers.

Some documentation indicated that once the package was compiled it was
theoretically possible to run the package in x64 mode and connect to x64
drivers; it's just not possible to do that while "developing" the
package in Visual Studio. I'm going to look further into that
possibility, but it will be a while before I have the time to do so.

Thanks again.

Joseph D. Wagner

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Trying to Connect SSIS to Oracle on x64

microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts

Re: Trying to Connect SSIS to Oracle on x64 - 07-10-2008 , 02:05 PM