locked
Executing Oracle SP in SSIS RRS feed

  • Question

  •  

    Hi

     

           I'm finding some challenges while executing a SP in SSIS (Execute SQL Task).

     

    Let me explain:

     

    I have following Package & SP in oracle DB(FYI I'm using Oracle 8i):

     

    CREATE OR REPLACE PACKAGE DBOWNER.TEST_PACKAGE_Client_Details AS

       TYPE GenericCursor IS REF CURSOR;

       PROCEDURE TEST_PROC (out_DEPT_TEST_cursor OUT GenericCursor);

    END;

    /

     

    CREATE OR REPLACE PROCEDURE DBOWNER.GetClientDetails (p_recordset OUT TEST_PACKAGE_CUSTOMER_DETAILS.GenericCursor) AS

    BEGIN

      OPEN p_recordset FOR

        SELECT *

        FROM   Client_Details;

    END GetClientDetails;

    /

     

    In Execute SQL Task in SSIS

     

    I have following statement in SQL Statement Property

     

    {call GetCustomerXMLDetails} 

     

    *One thing I didn’t understand here is do we need to pass any parameter (Because the accept a output parameter)

    But I’m not getting any error in design time (Query Parse) or runtime. 

     

    And in Result Set I have a variable (objClientDetails ) which is object type and result name is set 0(Zero).

     

    I’m not getting any error while executing the Package but the problem is I don’t how to use that variable (objClientDetails) because its returning as a object which I cannot convert to DataSet (Remember if you call a SQL Server SP you can convert object into Dataset so that we can use it in the Script Task or in For-Each Loop Container).

     

    Any help will be handy. 

     

    Note: I have used both OLE DB and ODBC connection type in Execute SQL Task.

    Wednesday, September 24, 2008 3:46 PM