THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Jorg Klein

Jorg Klein, Microsoft Business Intelligence consultant/architect from the Netherlands

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

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.

Published Thursday, June 09, 2011 12:26 PM by jorg

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Joao Picarra said:

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?

January 6, 2010 11:57 AM
 

jorg said:

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

January 6, 2010 12:24 PM
 

Evald said:

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

January 7, 2010 7:08 AM
 

jorg said:

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

January 7, 2010 9:38 AM
 

Albert said:

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.

February 18, 2010 1:20 PM
 

jorg said:

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

February 23, 2010 5:35 AM
 

Raja Kumar said:

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?

April 1, 2010 5:45 PM
 

Gordo said:

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...

May 26, 2010 4:44 PM
 

jorg said:

@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.

May 27, 2010 5:08 AM
 

Bildos said:

How to configure it for following configuration:

W2K3 SP2 x64 + SQL 2005 (32bit)  ?

September 19, 2010 5:07 AM
 

cda said:

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!

December 9, 2010 3:07 PM
 

cda said:

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!

December 9, 2010 4:02 PM
 

Øyvind said:

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

January 6, 2011 7:29 AM
 

Anonymous 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.

January 14, 2011 12:41 AM
 

Daniel said:

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.

February 21, 2011 7:05 AM
 

Charlotte said:

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 !

April 15, 2011 3:18 AM
 

Mike Milligan said:

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

June 9, 2011 4:33 PM
 

mskrzos said:

worked for me, thanks Jorg!

June 30, 2011 7:23 AM
 

David Hay said:

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!

July 22, 2011 8:36 AM
 

Paolo said:

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

August 22, 2011 8:48 AM
 

saro said:

This is very nice post,it was very useful  ..thank u very much

September 8, 2011 4:27 AM
 

Parthiban said:

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!

September 28, 2011 8:44 AM
 

Dave Ruijter said:

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

September 30, 2011 8:59 AM
 

Dennis said:

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

October 31, 2011 2:34 AM
 

Josh said:

Great post!  Worked like a charm.

December 14, 2011 1:25 PM
 

Dmitrii Zavorotny said:

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.

January 26, 2012 3:15 PM
 

Yun said:

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.

May 8, 2012 1:19 PM
 

Rwoeke said:

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"

May 9, 2012 5:22 AM
 

Yun said:

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.

May 11, 2012 12:37 PM
 

Peter said:

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.

June 20, 2012 12:27 PM
 

Welsh said:

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?

July 6, 2012 10:20 AM
 

Welsh said:

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?

July 6, 2012 10:29 AM
 

hydraulic press said:

I admire the valuable information you offer in your articles.

I will bookmark your blog and have my children check up here often.

I am quite sure they will learn lots of new stuff here than anybody else

Thanks

July 26, 2012 6:58 AM
 

hydraulic press said:

I admire the valuable information you offer in your articles.

I will bookmark your blog and have my children check up here often.

I am quite sure they will learn lots of new stuff here than anybody else

Thanks

July 31, 2012 4:58 AM
 

Ron C said:

Thank you, spent hours on this with all sorts of random errors..

Well written solution that worked for us..

October 24, 2012 5:26 AM
 

PHYOE said:

Hi Jora,

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

October 24, 2012 5:35 AM
 

Purvi Patel said:

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"?!!!

November 6, 2012 4:25 PM
 

Phindile Mashapa said:

Jorg Klein you are a genius.

November 17, 2012 4:37 AM
 

Psyber Fox said:

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,

December 19, 2012 8:57 AM
 

Apne said:

@Psyber : What is the exact error that you are getting?

December 21, 2012 11:14 AM
 

Apne said:

@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.

December 21, 2012 11:16 AM
 

Anandan said:

Thanks solution working for me.

December 24, 2012 3:01 AM
 

Erik said:

@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.

April 2, 2013 9:06 PM
 

Halvor said:

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.

June 5, 2013 3:00 PM
 

Siva Vardhan said:

Great blog Jorg. Saved a lot of time for us.

July 20, 2013 5:21 PM
 

Tom Derby said:

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.

August 27, 2013 2:45 PM
 

Ali said:

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?

August 27, 2013 5:58 PM
 

Ali said:

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.

August 27, 2013 9:44 PM
 

Venkat said:

Jorg,

I have unique problem, SSIS 2010 we have the packages and connecting to Oracle 11g (on Linux). I am using Oledb for source and Attunity for destination. The issue is, after reading x number of records the package is not proceeding further. The source table has around 5 million records (8 columns).

I have tried changing the buffer size etc but still no luck.

We have installed SQL 2012 SP1 with rollup patches but still the issue is persisting.

I have tried simply to read from the table and write to a flat file and no no luck

Venkat

November 17, 2013 5:02 AM
 

Murthy said:

Thanks Jorg, now i'm able to connect oracle and getting data in SSIS

January 21, 2014 8:13 AM
 

QuietG said:

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).

January 25, 2014 12:41 PM
 

Rams said:

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  

July 2, 2014 4:20 AM
 

Kleber Brunelli said:

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

July 3, 2014 3:06 PM
 

Don B. said:

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?

August 25, 2014 1:23 PM

Leave a Comment

(required) 
(required) 
Submit

About jorg

Jorg Klein, Microsoft Business Intelligence consultant/architect from the Netherlands.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement