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

Viewing 10 posts - 46 through 54 (of 54 total)

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

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

prvmine (7/9/2012)
the correct file name for the 32 bit 11g release 1 is win32_11gR1_client, which is version (11.1.0.6.0)
looks like you can get the client from here --
https://forums.oracle.com/forums/thread.jspa?threadID=944543
-- I don't have the exe/zip files anymore --

That install does not work with Windows Server 2008 R2.

Checking operating system requirements ...

Expected result: One of 5.0,5.1,5.2,6.0

Actual Result: 6.1

Check complete. The overall result of this check is: Failed <<<<

Problem: Oracle Database 11g is not certified on the current operating system.

Recommendation: Make sure you are installing the software on the correct platform.

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/

prvmine SSCertifiable Points: 7007 More actions · Answer 2

sorry - I don't have any more suggestions other than trying the alternative Jo Pattyn suggested

http://www.sqlservercentral.com/Forums/Topic1077381-1044-1.aspx

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

Sorry. I should have posted that I got this to work frst thing yesterday morning by launching the UI from the command line. No need for an old version.

C:\Oracle_Client\32bit\client\SETUP.exe ORACLE_HOME="D:\ORACLE\Product\11.2.0\client_32" ORACLE_HOME_NAME="OraClient11g_home2"

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/

prvmine SSCertifiable Points: 7007 More actions · Answer 4

thanks for the info, great that you got is working!

farooq.md SSC Veteran Points: 214 More actions · Answer 5

Lowell - Tuesday, July 3, 2012 6:16 AM
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.

hi Lowell,

one weird thing I noticed working with both providers for same package, Microsoft provider gives the correct data count while Oracle give incorrect data count for a query where filter is applied on date column. Any idea????

Lowell SSC Guru Points: 323518 More actions · Answer 6

farooq.md - Tuesday, March 14, 2017 8:01 AM
Lowell - Tuesday, July 3, 2012 6:16 AM
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.
hi Lowell,
one weird thing I noticed working with both providers for same package, Microsoft provider gives the correct data count while Oracle give incorrect data count for a query where filter is applied on date column. Any idea????

farooq I had not previously encountered any row count differences, but I had certainly been in situations where if the data was returning lob-type columns, i'd get errors.
In my last two positions, I've been in Microsoft only shops, so I can't give any recent, relevant experiences, sorry!

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!

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 7

farooq.md - Tuesday, March 14, 2017 8:01 AM
Lowell - Tuesday, July 3, 2012 6:16 AM
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.
hi Lowell,
one weird thing I noticed working with both providers for same package, Microsoft provider gives the correct data count while Oracle give incorrect data count for a query where filter is applied on date column. Any idea????

I am not an expert in this by any means, but as a thought could it be that your date filter is being inclusive in one provider and exclusive in the other? Can you verify which data is missing and see if it is one of the edge cases?

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

farooq.md SSC Veteran Points: 214 More actions · Answer 8

Lowell - Tuesday, March 14, 2017 8:14 AM
farooq.md - Tuesday, March 14, 2017 8:01 AM
Lowell - Tuesday, July 3, 2012 6:16 AM
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.
hi Lowell,
one weird thing I noticed working with both providers for same package, Microsoft provider gives the correct data count while Oracle give incorrect data count for a query where filter is applied on date column. Any idea????
farooq I had not previously encountered any row count differences, but I had certainly been in situations where if the data was returning lob-type columns, i'd get errors.
In my last two positions, I've been in Microsoft only shops, so I can't give any recent, relevant experiences, sorry!

np , Thanks for the reply, have a good day!!!

farooq.md SSC Veteran Points: 214 More actions · Answer 9

bmg002 - Tuesday, March 14, 2017 10:13 AM
farooq.md - Tuesday, March 14, 2017 8:01 AM
Lowell - Tuesday, July 3, 2012 6:16 AM
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.
hi Lowell,
one weird thing I noticed working with both providers for same package, Microsoft provider gives the correct data count while Oracle give incorrect data count for a query where filter is applied on date column. Any idea????
I am not an expert in this by any means, but as a thought could it be that your date filter is being inclusive in one provider and exclusive in the other? Can you verify which data is missing and see if it is one of the edge cases?

Thanks for replying, the DFT has dynamic query which pulls date from database and then puts it in filter to use from variable, the output of the date extracted acts different in both providers e.g. 14-Mar-2017 gives correct result with Microsoft provider but gives different count with Oracle.

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

I have better luck using the SQL Server Provider but my guess is you all know something more than me, Perhaps it depends on what you are trying to do.

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/