SSIS – Connect to Oracle on a 64-bit machine (Updated for SSIS 2008 R2)

Posted by

We recently had a few customers where a connection to Oracle on a 64 bit machine was necessary. A quick search on the internet showed that this could be a big problem. I found all kind of blog and forum posts of developers complaining about this. A lot of developers will recognize the following error message:

Test connection failed because of an error in initializing provider. 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.


After a lot of searching, trying and debugging I think I found the right way to do it!

Problems

Because BIDS is a 32 bit application, as well on 32 as on 64 bit machines, it cannot see the 64 bit driver for Oracle. Because of this, connecting to Oracle from BIDS on a 64 bit machine will never work when you install the 64 bit Oracle client.

Another problem is the “Microsoft Provider for Oracle”, this driver only exists in a 32 bit version and Microsoft has no plans to create a 64 bit one in the near future.

The last problem I know of is in the Oracle client itself, it seems that a connection will never work with the instant client, so always use the full client.
There are also a lot of problems with the 10G client, one of it is the fact that this driver can’t handle the “(x86)” in the path of SQL Server. So using the 10G client is no option!

Solution

  • Download the Oracle 11G full client.
  • Install the 32 AND the 64 bit version of the 11G full client (Installation Type: Administrator) and reboot the server afterwards. The 32 bit version is needed for development from BIDS with is 32 bit, the 64 bit version is needed for production with the SQLAgent, which is 64 bit.
  • Configure the Oracle clients (both 32 and 64 bits) by editing  the files tnsnames.ora and sqlnet.ora. Try to do this with an Oracle DBA or, even better, let him/her do this.
  • Use the “Oracle provider for OLE DB” from SSIS, don’t use the “Microsoft Provider for Oracle” because a 64 bit version of it does not exist.
  • Schedule your packages with the SQLAgent.

Background information

  • Visual Studio (BI Dev Studio)is a 32bit application.
  • SQL Server Management Studio is a 32bit application.
  • dtexecui.exe is a 32bit application.
  • dtexec.exe has both 32bit and 64bit versions.
  • There are x64 and x86 versions of the Oracle provider available.
  • SQLAgent is a 64bit process.

My advice to BI consultants is to get an Oracle DBA or professional for the installation and configuration of the 2 full clients (32 and 64 bit). Tell the DBA to download the biggest client available, this way you are sure that they pick the right one 😉

Testing if the clients have been installed and configured in the right way can be done with Windows ODBC Data Source Administrator:
Start…
Programs…
Administrative tools…
Data Sources (ODBC)

ADITIONAL STEPS FOR SSIS 2008 R2

It seems that, unfortunately, some additional steps are necessary for SQL Server 2008 R2 installations:

1. Open REGEDIT (Start… Run… REGEDIT) on the server and search for the following entry (for the 32 bits driver): HKEY_LOCAL_MACHINE\Software\Microsoft\MSDTC\MTxOCI
Make sure the following values are entered:

image

2. Next, search for (for the 64 bits driver): HKEY_LOCAL_MACHINE\Software\Wow6432Node\Microsoft\MSDTC\MTxOCI
Make sure the same values as above are entered.

3. Reboot your server.

72 comments

  1. My issue goes like this…I have VS2008 installed.  I have both the 32bit and 64bit Oracle clients installed.  I can connect to Oracle via a published website on this machine, but I cannot connect to Oracle when running in debug from VS2008.  Thoughts?

    Like

  2. Hi,
    How do I install both 32bit and 64bit Oracle clients?  I am encountering the same problem and I already have 64bit installed; however, when I try to install the 32bit, I get an error that the installer has detected an already existing instance on the computer and will not allow me to proceed.  
    Thanks.
    Wilbert

    Like

  3. Working on getting my oracle connections to work again.  We were running SQL Server 2012 and using VS2010 and I had all my SSIS projects working just fine.  Upgrading the SQL server to 2014 is require us to update VS to 2013 so we can keep working with SSIS.  The problem is now SSIS no longer connects to Oracle.
    I know for the SQL 2012/VS2010 combination all i had to install with the ODTwithODBC for Oracle and optionally the AttunitySSISAdaptor and it was working wonderfully.
    Any help will be appreciated.

    Like

  4. Thanks for help, please find below what I did to make SSIS working with Oracle DB.
    I installed all the below software:
    Sql Server 2012 64bit Enterprise edition installed fully
    windows server 2008 R2 64bit
    Visual studio 2010 service Pack 1
    Oracle client 32bit
    Oracle Client 64bit
    Atunity Oracle driver v2.0
    Oracle Rac 11g R2
    I installed all of the above software with Admin privileges and restarted my server and everything worked after that.
    what I noticed as one of the benefits of SSIS 2010 over SSIS 2008 is that if you are using Oracle OLE DB provider you can configure it to generate the destination table and the generated Table scripts will require very less modifications than the one generated from SSIS 2008 as datatypes are correctly matching the ones identified in Oracle.

    Like

  5. I have installed ODAC 11.2.0.3 64 bit on local machine and i have oracle client 11.2.0.3 installed on my local machine moreover OraOLEDB.oracle installed on SQL server.After the installation SQL server also rebooted but i am not able to see oracle OLEDB provider under the SSDT_BI tools (SSIS) for OLEDB source.
    Thanks in advance

    Like

  6. I am facing same issue in my laptop 64 bit, I installed Visual studio 2015 and relevant SSDT set up but I cannot connect to oracle data base  can some help me is resolving this by providing step  by step by procedure

    Like

  7. This is a horrible post, essentially you are saying….go find someone else to do this for you…is that the only info you are offering?  Oracle is a pain in the a$$, I ‘ve never understood why people think it is so great if you need years of skills just to connect to the product.  

    Like

  8. Here are some valuable tidbits :
    When installing the full 64bit and 32bit clients, choose the admin option.  It won’t work with the instant version.
    Use the deinstall batch file to remove previous Oracle client installations.  That can be found in the client folder.  I found a reboot was needed if you could not delete the remaining files in the Oracle folder.
    In BIDS, make sure debugging option 64 is set to FALSE if testing from BIDS.
    For SQL agent job, set the option for 32 bit runtime.

    Like

  9. Thank you very much Klein.. really helpful for me… was stuck at this point.. spent almost 1 day… your article helped me a lot… thank you so much

    Like

  10. Hi,
    I am trying to load data from oracle 11g to sql database.
    I am using VS 2010 to develop my SSIS package.
    I am not able to connect to oracle database. it seems that I am missing OLE DB provider for oracle.
    Can some one guide me what is the steps to download , install and configure the OLE DB provider for my SSIS package.
    There are so many sets of instruction you see on internet and difficult to decide which one is most accurate.
    Thanks in advance.

    Like

  11. I know everyone loves the 64/32 bit problems but I think for most people this problem is simpler than that.   I got the error message he shows above on a new machine.  In my case the Oracle provider wasn’t in the pick list shown for an oledb stage so I picked “Microsoft for Oracle and got the message he shows above. At the time I got the message  I had the Oracle 12c client installed, I could use sql developer, I could use sqlplus, my c# extracts against oracle worked but I was getting the message he showed above when I tried to access Oracle from sis 2012.  To use SSIS against Oracle you HAVE to install ODTwithODAC12….   .  When I did that everything just started working against Oracle without any 64 bit/32 bit gyrations.   I knows someone above recommends that but I think it bears repeating and being said more plainly.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s