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/

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!

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/

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

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/

have a look at
alternative

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/

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/

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/