execute oracle procedure from SSIS RRS feed

  • Question

  • we are connecting started to working in SSIS using Oracle Stored Procedure.

    in which i connected Oracle server in SSIS.

    in data flow i have added the connection and Oracle have connected.

    when i goto SQL Command option using oracle Commands its not working

    i get 

    Exception from HRESULT: 0xC020204A

    Error at Data Flow Task[OLE DB Source[1]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred.

    Error code: 0x80040E4A.

    An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80040E4A Description: "Command was not prepared.".

    Error at Data Flow Task [OLE DB Source[1]]: Unable to retrieve column information from the data source. Make sure your target table in the database is available.

    this is the error iam getting.

    Code : 

    set serveroutput on
    var c refcursor;
    execute [sp_Name]('SPY.P', to_date('2017-10-30','yyyy-mm-dd'), 'ETF', 'Creation', 'Close', :c);
    print c;

    please do advise on the mentioned issue. {iam not using commands in script}

    Using SSIS calling Oracle SP output in excel and archive the excel and email task.

    Wednesday, November 22, 2017 1:52 PM

All replies

  • Hi Eswaran,

    As far as I remember, OLEDB cannot populate a cursor inline.

    You need to rewrite the procedure to emit the result inside it via the return statement.




    • Proposed as answer by Visakh16MVP Wednesday, November 22, 2017 2:53 PM
    Wednesday, November 22, 2017 2:27 PM