2005 SSIS package executing Oracle procedure to return record set

  • Hi All,

    I'm trying to call an Oracle procedure from an SSIS OLE DB source data flow task and have a record set returned so i can write it to a flat file destination.

    When i try to preview it, i get the error:

    "No disconnected record set is available for the specified SQL statement."

    Any ideas? thanks, Scott

    Below is the statement i'm using to cal it and the procedure..

    DECLARE

    TYPE Cursor_Type_2 IS REF CURSOR;

    precordset Cursor_Type_2;

    BEGIN

    avmindev.freedomex.freedomexport(

    pdate=>TO_DATE('2012-03-08 00:00:00', 'YYYY-MM-DD HH24:MI:SS'),

    pstore_number=>471,

    precordset=>precordset);

    :PRECORDSET := PRECORDSET;

    END;

    PROCEDURE FreedomExport

    (pDate IN DATE,

    pStore_Number IN NUMBER,

    pRecordSet OUT SYS_REFCURSOR)

    AS

    BEGIN

    OPEN pRecordSet FOR

    SELECT

    SUBSTR(f2.quickid,1,4) as StudentID,

    SUBSTR(t.ticketid,1,7) as InvoiceNum,

    'I' as TicketType,

    SUBSTR(s.quickid,1,5) as ServiceID,

    TO_CHAR(sysdate,'YYMMDD-HH24MISS') as BatchID,

    ' ' as Grade,

    'XXXXXXXXXXXXXXXX' as Filler

    FROM TicketData t

    LEFT JOIN Service s ON t.ItemID = s.ID AND t.Store_Number = s.Store_Number

    LEFT JOIN Staff f2 on t.staffid2 = f2.id

    WHERE t.Status=1

    AND t."Date" = pDate

    AND t.datatype = 5

    AND t.store_number = pStore_Number

    AND NOT f2.ID is Null;

    END; /* FreedomExport */

Viewing 0 posts

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