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

  • Welsh Corgi

    SSC Guru

    Points: 116520

    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: 6968

    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

    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: 6968

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

  • farooq.md

    SSC Veteran

    Points: 214

    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: 323349

    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!

  • bmg002

    SSC-Insane

    Points: 22201

    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?

  • farooq.md

    SSC Veteran

    Points: 214

    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

    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

    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/

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

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