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.

73 comments

  1. I have that problem, but I have one machine with WServer 2003 64 with SQL 64. To connect to other machine with Linux 64 and Oracle 64.
    Will this solution work with this plataforms?

    Like

  2. Hi Joao,
    The solution described in this blog post should work for your architecture. The software needs to be installed on the machine that runs the SSIS package, in your case this will be the WServer 2003 machine.
    I’ve never tested it though, so let us know if it worked!
    -Jorg

    Like

  3. Hi Evald,
    Your URL goes to a page with “Instant Clients”, as you can read in the blog post (“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.”), the instant client is not sufficient.
    I never installed the full client myself because in my opinion it’s something an Oracle specialist must do, which I am not.
    This is Oracle’s Technology download page:
    http://www.oracle.com/technology/software/index.html
    A quick search on google points to the following page as the “full client” download:
    http://www.oracle.com/technology/software/products/database/index.html
    Correct me if I’m wrong though
    -Jorg

    Like

  4. Jorg, In whick folder I need to install the full Oracle Client Рin the Oracle folder or the SQL server folder?  I already have Oracle server installed this machine with two other databases on a 64bits Windows 2003- which has both Oracle and SQL Server.

    Like

  5. Hi Albert,
    Do you mean that you have both SQL Server and Oracle installed on one server?
    If so, that’s a architecture I have never seen before, most of the times there are separate servers for SQL and Oracle. But I guess this should not be a problem.
    You don’t need to install the full client in the Oracle or SQL folder. You can just create a new folder for it, the location doesn’t matter because you need to point to it with a environment variable anyways.
    -Jorg

    Like

  6. I understand that we need to install full oracle client 32bit and 64bit at different ORACLE_HOME to resolve the issue.
    so during the design time, we use the 32 bit version of the oracle client and during runtime we use 64bit oracle client.  
    While switching from design to run time, do we have to change the connectivity or connection to use 64 bit  how?

    Like

  7. Are you saying we should download and install the 2 gigabyte full oracle database suite (which is what your link goes to) instead of just a client download? ¬†If so, there HAS to be a better solution…

    Like

  8. @Raja: You don’t need to take any action while switching from design time to run time. At design time the process is 32 bit and it can only “see” the 32 bit drivers. At run time the process is 64 bit, this process won’t see the 32 bit driver but will automatically pick the 64 bit driver.
    @Gordo: I only say that the solution I described in this blog post is a working solution. When I figured this out I could not get it to work with the instant client and there were a lot of other people that said the instant client was not sufficient.
    If you find a way to do it with the instant client right now, I would like to hear it! Maybe there is a newer version now, you can always give it a try.

    Like

  9. Can you please provide clarification on the following:
    ‘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.’
    I am setting up a data source from within Visual Studio 2008.  I have the following options for the Type of data source:
    Microsoft SQL Server
    OLE DB
    Microsoft SQL Server Analysis Services
    Oracle
    ODBC
    XML
    Report Server Model
    SPA NetWeaver BI
    Hyperion Essbase
    TERADATA
    GENEVA
    Firstly, do I pick “Oracle” or “OLE DB” from this list? ¬†(or something else)
    If I pick OLE DB, my understanding is that I can specify either:
    Provider=msdaora;Data Source=MySource;
    for “OLE DB Provider for Oracle (from Microsoft)”
    Provider=OraOLEDB.Oracle;Data Source=MySource;
    for “OLE DB Provider for Oracle (from Oracle)”
    Which is the correct method?
    Thank you for any help!

    Like

  10. I figured it out – it’s “OLE DB” with this connection string:
    Provider=OraOLEDB.Oracle;Data Source=MySource;
    The Admin had neglected to reboot after installing the Oracle client, so none of the options were working before.
    Thanks again for your informative blog!

    Like

  11. Hi,
    This solution did not work. “Test connection” seems to work fine when setting up the data source, but when we run the SSIS package, an Oracle TNS error occurs

    Like

  12. Oyvind, how are you running the SSIS package?  If through BIDS, make sure you have set the Debugging option RunAs64Bit to False.  Remember that BIDS is 32-bit.

    Like

  13. Hi everybody. I have a similar problem, and I can describe it more accurately if necessary.
    I have developed a dtsx with BIDS. It reads data from Oracle. I can execute it from BIDS.
    If I close the package (but not BIDS), then I open the package again and I run it, it works.
    If I close the package, I close BIDS, and finally I open the package again then a validation error appears in every data flow task that uses the Oracle connection. If I open the data flow task and I edit the box that connects to Oracle and make a preview then it works, the error messages turn into warnings and the package can be executed.
    Any idea with this strange issue?
    Thanks for your time. Best regards.

    Like

  14. Hi !
    I had the same problem as you describe on this page and I did all the procedure. It works with SSIS but not with SSAS, does anybody know why ? Do you have a solution ?
    Thanks !

    Like

  15. First off, love your blog.  Thanks for sharing!!!
    The Microsoft Oracle connectors are terribly slow.  My tests took 10 minutes 45 seconds to load 45,000 rows, 2 columns round circle (from MS SQL Server to Oracle and back.)
    Using Attunity drivers that I downloaded from Microsoft (see my blog) I accomplished same in 45 seconds.
    Here’s a Funny quote:
    “I do not expect that Microsoft will write an Oracle fast loader – currently it comes in around number 999 in my list of 1000 features for next version, just slightly ahead of recompiling for Linux.” – Donald Farmer
    Please visit my first blog post ever that I just created to address this very subject.
    http://bidn.com/blogs/MMilligan/bidn-blog/1878/ssis-and-oracle-connections

    Like

  16. Whoever is anonymous is who said “Oyvind, how are you running the SSIS package? ¬†If through BIDS, make sure you have set the Debugging option RunAs64Bit to False. ¬†Remember that BIDS is 32-bit.” is a life saver! ¬†I’ve been banging my head on this for 3 days!

    Like

  17. Thank you! I could not use MSSQL Import using OLE provider for Oracle.
    Installing both the Oracle clients (32 and 64) + registry settings suggested provided a full working Oracle OLE DB Provider.
    Thanks again

    Like

  18. Hi,
    I have a package which loads data from Oracle’s source query to SQL Table.
    When i edit the source box , i can see teh columns and test connection is successful for Oracle. but , when i run the package, it is throwing this error. \
    //[OLE DB Source [1240]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. ¬†The AcquireConnection method call to the connection manager “OracleConnection” failed with error code 0xC0202009. ¬†There may be error messages posted before this with more information on why the AcquireConnection method call failed.
    ///
    i am running it on 64bt server. some one pl help. Thanks!

    Like

  19. @Parthiban, it seems like a 64bit compatability failure. Can you check this by testing the package in x86 mode (change SSIS project setting)?

    Like

  20. Hi,
     I am encountering the same issue, trying to connect to oracle using sql server management studio.  if the following is my tnsnames file
    MY_TNS_ALIAS =
     (DESCRIPTION =
       (ADDRESS_LIST =
         (ADDRESS = (PROTOCOL = TCP)(HOST = SSAPQA01)(PORT = 1527))
       )
       (CONNECT_DATA =
         (SID = Q01)
       )
     )
    what should i key into the Data Source and Privider String when i set up the linked server.
    Thanks

    Like

  21. If you don’t want to mess with the registry key, I found that if you install Oracle Client 11g R2 64 bit but Oracle Client 11g R1 32 bit, there are no issues. ¬†I only have issues when I install R2 for both 32 and 64 bit. ¬†I don’t remember for sure, but installing R1 for both 32 and 64 bit also had the same issue. ¬†By installing different versions, SSIS picks up the 32 bit driver without issues and SQL Agent picks up the 64 bit driver due to a lack of registry conflicts during the install.

    Like

  22. Can anyone tell me how to install oracle client 32bit and 64bit at different ORACLE_HOME ?
    This is what I did.
    Install 64 bit client first at C:\Oracle using Oracle Universal Installer.
    After it’s done, under HKEY_LOCAL_MACHINE|SOFTWARE|ORACLE|KEY_OraClient11g_home1
    ORACLE_HOME was set to C:\Oracle\Product\11.2.0\client_1
    When I clicked Start | Oracle-OraClent11g_home1 | Applicaiton Development | SQL PLUS, it pointed to C:\Oracle\Product\11.2.0\client_1\BIN\SQLPLUS.EXE (64 bit product)
    Then I installed 32 bit client at C:\Oracle32
    After it’s done, under HKEY_LOCAL_MACHINE|SOFTWARE|Wow6432Node|ORACLE|KEY_OraClient11g_home1 (still home1, not home 2):
    ORACLE_HOME was set to C:\Oracle32\Product\11.2.0\client_1
    When I clicked Start | Oracle-OraClent11g_home1 | Applicaiton Development | SQL PLUS, it is now pointed to C:\Oracle32\Product\11.2.0\client_1\BIN\SQLPLUS.EXE (32 bit product)
    It looks like I have only one ORACLE_HOME, although I have installed two clients.
    Another question. Does the install order matter? Which client should I install first?
    Any help will be greatly appreciated.

    Like

  23. HI, I have the same issue as √ėyvind.
    I installed both clients 32 and 64bit. It resolves when i test connection. As soon as I run the package I get the TNS cannot resolve issue.
    Debugging 64bit is set to false.
    Any Idea what it could be?
    “This solution did not work. “Test connection” seems to work fine when setting up the data source, but when we run the SSIS package, an Oracle TNS error occurs”

    Like

  24. I have solved my problem.
    Need to run installer from command line to set ORACLE_HOME_NAME when installing clients 11g.
    setup.exe ORCALE_HOME_NAME=”oracle_home_name”
    Otherwise the second client takes the home of the first client.

    Like

  25. This specific issue (BIDS 32 bit, SQL Server 64bit and Oracle dB)has had me baffled for the last 10 days until now. Can’t thank you enough for the information you provided.

    Like

  26. noticed that the environmental ORACLE_HOME does not have a value.
    In the registry named HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraClient11g_home1
    The ORACLE_HOME entry contains the first installation which is the 64 bit installation: D:\Oracle\product\11.2.0\client_1
    I had to install the 64 bit version first due to a bug because when I installed the 32 bit version first, when I tried installing the 64 bit version the architecture test failed and it detected the machine as 32 bit.
    There are no entries for the second installation which is the 32 bit version and that is what BIDS and SQL Plus use. SQL Server Agent uses the 64 bit version.
    I did not reboot after installing client_1 (64 bit) and before installing client_2 (32 bit). Maybe that is contributing to the problem?

    Like

  27. How do you run the intaller form the command line and specfy the Oracle_Home_Name? What are the values for the Oracle_Home_Names?

    Like

  28. Hi Jorg,
    I just find out your post. Before that I got big headache for 4 days (day n night) to solve this issue. Thanks for your post. But one thing I would like to ask is, When I deploy my Package at SQL Agent Job, I face this issue.
    “SSIS Error Code DTS_E_OLEDB_NOPROVIDER_64BIT_ERROR. ¬†The requested OLE DB provider MSDAORA.1 is not registered — perhaps no 64-bit provider is available. ”
    Please help me is there anything miss out to configure?
    I already configure my server that you mention on your post.
    Regards,
    Phyoe

    Like

  29. Hi there,
    I am trying to connect SQL 2008 R2 to an Oracle 8i (8.1.7.3) database. I have installed both the 32- and 64-bit clients (11g), enabled the setting on my Oracle Provider Properties and have restarted the server. I still cannot connect either via a Linked Server or an OLE connection in VS2008, getting an error regarding the listener.
    I am at wits’ end and rather frustrated – can you possibly assist?
    Thank you kindly,

    Like

  30. @Purvi, @Apne:  Did you ever find a way to use the Attunity provider with the Execute SQL Task? I would love to use that driver if possible.

    Like

  31. Thanks Jorg
    It was informative. An alternative to consider is to have a developer machine with the 32 bit Oracle client that is deploying to the production server with 64 bit Oracle.

    Like

  32. I’ve stopped doing the full Oracle client loads on SQL Server servers in favor of the ODAC (Oracle Data Access Components). It’s everything you need on a Windows server.

    Like

  33. Hi Jorg,
    I have followed the instructions but I still get errors running the package for SQL Server Agent. I can however, run the package fine from BIDS which means the 32-bit side works fine.
    Here is what I did:
    Installed and configured 32-bit Oracle client (admin mode)
    Installed and configured 64-bit Oracle client (admin mode)
    Reboot
    Changed registry keys and reboot
    Used the Oracle provider for OLE DB
    Any ideas?

    Like

  34. With the steps above, both 32 and 64 bit work fine for the Oracle .Net provider and Attunity Oracle Connector. However, I wasn’t able to get it to work using OLEDB provider for 64-bit as mentioned in the post.

    Like

  35. Thank you Korg for creating this post. When I recently ran into an issue where  I was able to successfully create and execute a package using the 64-bit Import/Export Wizard to import data from an Oracle instance, and then upon upon opening that same package in BIDS, would receive all types of errors, I concluded it must be something specific to BIDS.  This would occur on our server, but not at my local workstation (where apparently the 32-bit drivers are installed).

    Like

  36. HI..
    I am trying to connect ORACLE 11G (64Bit) DB installed on my computer from Visual Studio 2010. Through
    Tools-> connect to Database
    Data Source= Oracle database
    Data provider= .NET Framework provider for OLE DB
    and after “Test Connection” I am getting error “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.”
    Please guide me to resolve….
    Thanks  

    Like

  37. Jorg,
    Just would like to thank you very much for that. I had an issue with SSRS and Oracle and your tutorial solved the issue.
    Best Regards,
    Kleber

    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 )

Connecting to %s