 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / Services / DTS / July 2008Trying to Connect SSIS to Oracle on x64
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 On Jul 3, 10:50 am, "Joseph D. Wagner" <the...@josephdwagner.info> wrote: > Oh great magi of Microsoft and Oracle, please help me! > [quoted text clipped - 78 lines] > > Joseph D. Wagner Hi Joseph, I don't know if this can help you... i had a similar problem with a pkg trying to save data to excel: executed in visualstudio it worked, executed from a job it didn't. Launching from inside the job with cmdexec it worked. The problem was with 64 bit sqlserver (apart oracle, i've your same architecture) that cannot connect to 32bit jet oledb drivers, or with dtexec utility that used 64bit drivers, or somewhat... When i was searching for a solution i've read somewhere that pkg with oracle connections had a similar resolution as well. Check it out! http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1764293&SiteID=1 http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=159581&SiteID=1 hope this helps! M. On Jul 3, 10:50 am, "Joseph D. Wagner" <the...@josephdwagner.info> wrote: > Oh great magi of Microsoft and Oracle, please help me! > [quoted text clipped - 78 lines] > > Joseph D. Wagner From MsTechnet: Integration Services Considerations on 64-bit Computers http://technet.microsoft.com/en-us/library/ms141766.aspx let us know M. > On Jul 3, 10:50 am, "Joseph D. Wagner" <the...@josephdwagner.info> > wrote: [quoted text clipped - 87 lines] > 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 somebody found a solution for this issue? regards, DM > > On Jul 3, 10:50 am, "Joseph D. Wagner" <the...@josephdwagner.info> > > wrote: [quoted text clipped - 107 lines] > > Joseph D. Wagner 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/library/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 > somebody found a solution for this issue? > [quoted text clipped - 123 lines] >> >> Joseph D. Wagner |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.