Executing an oracle stored procedure from SSIS

Hello,

Is there an oracle provider out there that will let me invoke a parameterless stored procedure that is in a package in my Oracle source?

Better could that stored proc receive a prameter?

Still better, could I use a stored proc in a OLEDB source component and get the resutls from its only out variable (ref cursor) into my SSIS dataflow?

I haven't been able to get any of these basic functionalities working with either the Oracle OLEDB or the Microsoft OLEDB for Oracle provider...

If not, are there any plans to enahnce the MS provider to handle that?

A more tricky question :
Why does the ReportingService data processing extension for Oracle sources allow such things and not the .NET provider in SSIS?

Thanks

Hi,

I can attempt to provide the beginning of an answer.

I use an ADO.NET OracleClient data provider in ssis package data Flow to access Oracle 8 where I have a Function that I call this way

SELECT ONE.TABLE_READY('Customer_Restated') as Ready FROM DUAL

For stored procedures it works like that in Toad but I cannot confirm if it works from SSIS.

Call one.verify_job_completion('AA') ;

Philippe

|||

Thanks Philippe,

I had tried that but my stored proc does inserts/updates, so I can't "hide" it inside a function, otherwise I get my "ORA-XXXX can't do DML inside DQL..."

I made that work with ADO.NET and IsQureyStoredProc=True on the ExecuteSQLTask, but still struggling with parameters and stored procs that return cursors (data into my dataflow).

sql