THE SQL Server Blog Spot on the Web

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

Jorg Klein

Microsoft Data Platform MVP from the Netherlands

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

You can read this blog post at this link:

This blog has moved to There will be no further posts on Please update your feeds accordingly.

You can follow me on twitter:

Published Thursday, June 9, 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



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!


January 6, 2010 12:24 PM

Evald said:


do you mean to use the clients on this page


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:

A quick search on google points to the following page as the "full client" download:

Correct me if I'm wrong though


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.


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


Microsoft SQL Server Analysis Services




Report Server Model

SPA NetWeaver BI

Hyperion Essbase



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:


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.

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:


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:


 I am encountering the same issue, trying to connect to oracle using sql server management studio.  if the following is my tnsnames file







     (SID = Q01)



what should i key into the Data Source and Privider String when i set up the linked server.


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


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


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.



October 24, 2012 5:35 AM

Purvi Patel said:

I have followed 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 ( 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.

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)


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:


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


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:


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


July 2, 2014 4:20 AM

Kleber Brunelli said:


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,


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

Wilbert said:


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.  



December 18, 2014 1:48 AM

Abdul Hameed said:

Thanks it resolved my issue.  After editing registry values.. Thanks alot

January 16, 2015 5:22 AM

cristian said:


How to run ".dtsx" in sql server agent?

January 21, 2015 3:26 PM

hp said:


how to configue connection manager about connecting non-standard port oracle by SSIS


February 13, 2015 2:47 AM

Arshpreet said:

Thank you for your details on this. It worked for me as well. Thanks a lot.

May 11, 2015 3:33 PM

Ryan said:

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.

May 14, 2015 11:20 AM

Adel Morsy said:

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.

July 21, 2015 2:21 AM

That didn't help said:

At all...

August 13, 2015 3:31 PM

Jay said:

I have installed ODAC 64 bit on local machine and i have oracle client installed on my local machine moreover 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

November 18, 2015 3:06 PM

Jay said:

i am using VS 2013 which has SSDT - BI tools.

Can anyone has anyone answer it really damaging me a lot.

November 18, 2015 4:50 PM

Simplefix said:

use the ADO NET Souce instead of OLE DB Source will be the simplest way to fix it

November 24, 2015 8:55 AM

Daniel said:

Thank you so very much!!!

December 8, 2015 4:49 PM

Ravi said:

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

January 20, 2016 10:13 AM

Hikmer said:

This is a horrible post, essentially you are saying....go find someone else to do this for 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.  

January 26, 2016 11:21 AM

Mark said:

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.

February 19, 2016 6:33 PM

Subramanian Doraiswamy said:

Awesome articles.. clearly explains what to be done. Thanks Klein

May 20, 2016 6:31 AM

Alexy said:

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

September 5, 2016 2:22 AM

Bruno Torelli said:

Worked. Thanks

December 26, 2016 1:20 PM

Yue Ji said:

Now you can use Microsoft Connector for Oracle by Attunity to connect

March 2, 2017 3:04 AM

Sunita said:


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.

May 25, 2017 1:49 PM

Rasif Tahmid said:

Thanks a lot. It works for me.

August 1, 2017 3:45 AM

Flash Gordon said:

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.

August 4, 2017 4:13 PM

Leave a Comment


About jorg

Jorg Klein, Microsoft Data Platform MVP from the Netherlands.
Privacy Statement