Oracle's OLEDB Provider VS Microsoft OLEDB Provider for Oracle in SSIS

Question

Oracle's OLEDB Provider VS Microsoft OLEDB Provider for Oracle in SSIS

Welsh Corgi

SSC Guru

Points: 116520
More actions
July 3, 2012 at 5:25 am

#263256

It has been a couple of years since I worked with Oracle within SSIS and I seem to recall having preferred the Oracle OLEDB Provider over Microsoft's OLEDB Provider for Oracle.
Does anyone have any preferences? What are the pros & cons? I'm looking for speed.
To use the Oracle Provider you have to download the Oracle Client and perform an Administrative Installation.
Any thoughts, ideas or suggestions?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 15 posts - 1 through 15 (of 54 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

definitely you'll need to use the Oracle driver, and not the microsoft driver;

besides there not being a 64 bit version of the MSDAORA driver, there are a few other issues as well; that driver was last updated for Oracle Version 8, so

no TNS support.

I seem to rem3ember there being an issue with LCOBS/BLOBS being handled differently,a nd the provider having issues, but i'd have to gooogle that;

like you, it's been a while since i fiddled with Oracle and the MSDAORA; we swiched to the oracle provider a long time ago.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 2

Hey Lowell,

Thanks a lot.

Now I'm trying to remember which Oracle Download that I need. The Client Drivers only, etc?

I'm not sure and I would prefer to download the correct on on the first attempt.

I downloaded the Oracle Database 11g Release 2 Client (11.2.0.1.0) for Microsoft Windows (x64) from the following URL and the zip file was no good.

http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-win64soft-094461.html

Thanks.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

SQL Surfer '66 SSCertifiable Points: 5161 More actions · Answer 3

If my memory serves me right I think you need both 32- and 64-bit Oracle Client. If you're using BIDS (32-bit)you need 32-bit client to connect to the Oracle database. And a 64-bit client if SSIS is executed by SQL Server Agent (if you're running 64-bit SQL Server).

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 4

Yes, I'm running SQL Server 2008 R2 64 bit on Windows 2008 R2 64 bit.

I believe that you are right that I will need both 32 & 64 bit clients.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Jo Pattyn SSC-Dedicated Points: 32347 More actions · Answer 5

Jo Pattyn

SSC-Dedicated

Points: 32347

July 3, 2012 at 11:18 am

#1508373

have a look at

alternative

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 6

I'm definitely going with a Server OLEDB is fast and a Liked server to a remote server can be terribly slow.

Thanks

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

SQL Surfer '66 SSCertifiable Points: 5161 More actions · Answer 7

Jo Pattyn (7/3/2012)
have a look at
alternative

What alternative?

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 8

Jan.Sundbye (7/4/2012)
Jo Pattyn (7/3/2012)
have a look at
alternative
What alternative?

One Alternative to an OLEDB Source & Destination would be to setup a Linked Server and use OPENQURY.

I prefer to specify the Fast Load Option and uncheck Check Constraints on the OLEDB Destination to minimize the load time.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 9

Jan.Sundbye (7/3/2012)
If my memory serves me right I think you need both 32- and 64-bit Oracle Client. If you're using BIDS (32-bit)you need 32-bit client to connect to the Oracle database. And a 64-bit client if SSIS is executed by SQL Server Agent (if you're running 64-bit SQL Server).

Thanks. Do you have a reference to support this?

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 10

Double post.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 11

I found that the Installation for the Oracle10g Provider for OLE DB is probably what I need.

The download is at the following location:

http://www.oracle.com/technetwork/database/windows/utilsoft-088126.html

I run the install and I get the following error:

Oracle Universal Installer: The value of the environmental variable path is more than 1023 characters. This value cannot be set.

Looking for a solution to he

Check the path of the Environment Varible,if the path does not contain $oracle_home,such as d:oracleora81bin,you will get the error above.Just set the path manully,do check in the DOS command,type path:,if you see the $oracle_home is in the path it lists,then it'll be

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

SQL Surfer '66 SSCertifiable Points: 5161 More actions · Answer 12

Welsh Corgi (7/4/2012)
Jan.Sundbye (7/3/2012)
If my memory serves me right I think you need both 32- and 64-bit Oracle Client. If you're using BIDS (32-bit)you need 32-bit client to connect to the Oracle database. And a 64-bit client if SSIS is executed by SQL Server Agent (if you're running 64-bit SQL Server).
Thanks. Do you have a reference to support this?

The only reference I have is my own experience. First I installed a 64-bit Oracle client on a 64-bit SQL Server 2008. The developers using BIDS were unable to logon to the Oracle database. Installing a 32-bit Oracle client also enabled them to access the Oracle database.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 13

I found an article that confirms that the 64 and 32 bit Oracle Client must be installed.

http://sqlblog.com/blogs/jorg_klein/archive/2011/06/09/ssis-connect-to-oracle-on-a-64-bit-machine.aspx

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 14

I download the 32 & 64 bit Oracle Client Zip Files, attempt to extract and I get a message that they are empty.

I downloading from Oracle's Web Site.

I'm don't understand what the problem is?

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/