locked
SSIS - Oracle Provider error RRS feed

  • Question

  • Hello,

    I am doing simple load from Oracle to SQL Server, I am getting below error in the source connection.

    If I am using -  Provider OraOLEDB-     I get error at source -  convert between Unicode and non unicode string data types error

    If I am using -MSDAORA.1 -- I receive below error -

    [OLE DB Source [1]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "oracle1.scott1" failed with error code 0xC0209303.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.

    [SSIS.Pipeline] Error: component "OLE DB Source" (1) failed validation and returned error code 0xC020801C.

    [Connection manager "oracle1.scott1"] 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".

    Any help?

    Thanks


    John

    Monday, October 1, 2012 11:31 PM

Answers

  • Do you have the connection configured?

    To change the package in BIDS use


    Arthur My Blog

    • Marked as answer by John Fend Tuesday, October 2, 2012 2:32 PM
    Tuesday, October 2, 2012 1:10 AM

All replies

  • John hi,

    you either need to install the 64 bit Oracle driver, or instruct the package running in 32 bit mode (provided you have successfully configured the Oracle connectivity).

    How you run in 32 bit mode depends on how you execute the package.


    Arthur My Blog

    • Proposed as answer by SSISJoostMVP Tuesday, October 2, 2012 9:39 AM
    Tuesday, October 2, 2012 12:49 AM
  • Hi Arthur,

    How to install 4 bit oracle driver, or change pack config to 32bit ?

    Thanks.


    John

    Tuesday, October 2, 2012 1:01 AM
  • Do you have the connection configured?

    To change the package in BIDS use


    Arthur My Blog

    • Marked as answer by John Fend Tuesday, October 2, 2012 2:32 PM
    Tuesday, October 2, 2012 1:10 AM
  • ....and this if you running the package under a sql server agent job


    http://btsbee.wordpress.com/

    Tuesday, October 2, 2012 9:18 AM
  • Hi Arthur,

    How to install 4 bit oracle driver, or change pack config to 32bit ?

    Thanks.


    John


    And some more info and screenshot of changing to 32bit. For Oracle 64 drivers you should check Oracle.com

    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter



    Tuesday, October 2, 2012 9:39 AM
  • Thanks Arthur,

    It works.


    John

    Tuesday, October 2, 2012 2:32 PM