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:
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.
Jorg Klein's Blog 
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?
LikeLike
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
LikeLike
Hello,
do you mean to use the clients on this page http://www.oracle.com/technology/software/tech/oci/instantclient/htdocs/winx64soft.html
?
which to download?
thank you
LikeLike
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
LikeLike
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.
LikeLike
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
LikeLike
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?
LikeLike
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…
LikeLike
@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.
LikeLike
How to configure it for following configuration:
W2K3 SP2 x64 + SQL 2005 (32bit) ?
LikeLike
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!
LikeLike
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!
LikeLike
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
LikeLike
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.
LikeLike
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.
LikeLike
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 !
LikeLike
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
LikeLike
worked for me, thanks Jorg!
LikeLike
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!
LikeLike
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
LikeLike
This is very nice post,it was very useful ..thank u very much
LikeLike
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!
LikeLike
@Parthiban, it seems like a 64bit compatability failure. Can you check this by testing the package in x86 mode (change SSIS project setting)?
LikeLike
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
LikeLike
Great post! Worked like a charm.
LikeLike
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.
LikeLike
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.
LikeLike
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”
LikeLike
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.
LikeLike
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.
LikeLike
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?
LikeLike
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?
LikeLike
Thank you, spent hours on this with all sorts of random errors..
Well written solution that worked for us..
LikeLike
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
LikeLike
I have followed http://msdn.microsoft.com/en-us/library/ee470675%28v=sql.100%29.aspx link and I could successfully connect to Oracle Database using Oracle Source component provided by Attunity.
But now I can not map appropriate Connection type in Execute SQL Task.
Any Ideas? Or any other workaround to “Oracle provider for OLE DB”?!!!
LikeLike
Jorg Klein you are a genius.
LikeLike
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,
LikeLike
@Psyber : What is the exact error that you are getting?
LikeLike
@Purvi: There is an old post saying that we can’t use Attunity in Execute SQL TASK.
http://www.attunity.com/forums/microsoft-ssis-oracle-connector/execute-sql-task-1578.html
Let us know if you find a way to work around this.
LikeLike
Thanks solution working for me.
LikeLike
@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.
LikeLike
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.
LikeLike
Great blog Jorg. Saved a lot of time for us.
LikeLike
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.
LikeLike
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?
LikeLike
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.
LikeLike
Thanks Jorg, now i’m able to connect oracle and getting data in SSIS
LikeLike
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).
LikeLike
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
LikeLike
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
LikeLike