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!
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!
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.
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:
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:
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.
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?
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 it resolved my issue. After editing registry values.. Thanks alot
How to run “.dtsx” in sql server agent?
how to configue connection manager about connecting non-standard port oracle by SSIS
Thank you for your details on this. It worked for me as well. Thanks a lot.
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.
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.
I have installed ODAC 18.104.22.168 64 bit on local machine and i have oracle client 22.214.171.124 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
i am using VS 2013 which has SSDT – BI tools.
Can anyone has anyone answer it really damaging me a lot.
use the ADO NET Souce instead of OLE DB Source will be the simplest way to fix it
Thank you so very much!!!
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
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.
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.
Awesome articles.. clearly explains what to be done. Thanks Klein
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
Now you can use Microsoft Connector for Oracle by Attunity to connect
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.
Thanks a lot. It works for me.
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.
using Oracle provider for OLE DB in SSIS, is the data encrypted in transit.
Using SQL server 2016 in Azure VM to connect to Oracle 12g database on prem – the clients requirement is that the data needs to be encrypted – any suggestion?