Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

2005 SSIS package executing Oracle procedure to return record set Expand / Collapse
Author
Message
Posted Wednesday, April 10, 2013 10:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 20, 2013 2:22 PM
Points: 2, Visits: 22
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 */


Post #1440927
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse