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.
