none
SSIS 2008 Package with OLE DB provider MSDAORA.1 does not pass validation

    Question

  • Hello there---

    I have run into a problem that apparently others have hit, too, but I don't seem to see any definitive solutions to the problem... I have created a package in BIDS (VS2008) for SQL 2008, living on a Windows Server 2008 R2 box (64-bit OS). I have set up my connections to an Oracle DB and my local SQL Server via Connection Managers, and testing the connections rolls successfully. My Data Flow tab is all set with the source db (Oracle) and my destination db (SQL 2008) as well as a Data Conversion step in between. But when I run the package from within the BIDS (F5), the package does not pass validation. I am getting the following messages...

    [Connection manager "OTAP.rmarkarian"] Error: 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.  Error code: 0x00000000.
    An OLE DB record is available.  Source: "Microsoft OLE DB Service Components"  Hresult: 0x80040154  Description: "Class not registered".

    I have looked at the following thread in this forum (http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/920a0817-d9b8-4766-b0bf-baa6430acf93), but the reply marked as the solution does not fit my situation (I am not using SNAC provider). I double-checked the Oracle site to see if the Visual Studio developer tools (where the OLEDB providers for Oracle within VS are downloaded) are provided to see if there is a different download specific for 64-bit, bit there is only one version (http://www.oracle.com/technetwork/database/windows/downloads/index-101290.html)

    Any help with this issue would be great!

    Thanks!

    ---Keith

    Friday, December 03, 2010 7:13 PM

Answers

All replies

  • Hi Keith,

    MSDAORA is a 32-bit driver. You can switch to native oracle OLEDB driver (OraOLEDB.Oracle) - you'd have to install Oracle client on your target server. As far as I know, Oracle has different versions for 32 and 64 bit: http://www.oracle.com/technetwork/database/windows/downloads/index-090165.html

    Here is a note from the bottom of the above link:

    "- 64-bit Oracle 11g ODAC for Windows x64 is available on Oracle Metalink as part of Oracle Database downloads"

    As an alternative, you  can switch to ADO.NET provider.

    Hope that helps,

    Akim

    Friday, December 03, 2010 9:01 PM
  • To other battle-weary SSIS developers...

    I also have this issue but was driven to try the MSDAORA driver simply because 1) the 64 bit attunity driver for Oracle (which I normally use) doesn't have row buffer large enough with OCI call to handle one extremely large Oracle table/row, and 2) the 64 bit OLEDB  driver from Oracle mentioned above also can't read the table - preview shows "can't display" and then gives unicode errors on the destination. Oh, and the ado.net driver also has unicode issues.

    Why is Microsoft so slow in supporting 64 bit?  I don't get that.

    Be forewarned, just when you think one-driver-fits-all, you'll come across a situation that doesn't fit. I'm dealing with Oracle datatypes like sdo_geometry and clobs and giant denormalized tables. 

    My only workaround at this point for this table would seem to be to extract 1/2 the table's columns in 2 sources. 

    I would sure welcome any alternatives.

     

    Thanks, Gary


    Gary Melhaff, DW Architect, MCTS SqlServer 2008 BI, Developer
    Friday, February 18, 2011 5:00 PM
  • OK, have had the same issue, and here is the easy way past this problem. In your package job go to execution options and check the 32 bit box. Then it wll use the 32 bit version and work. Try it out, works great. No fuss no muss!

    Regards!

    Friday, May 16, 2014 3:27 PM