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

ExecuteWithResultsAndMessages2, syntax problem?

Here is a code snippet that I am using from Ken's sp_run_xml_proc.
I want to modify it, such that when I invoke sp_run_xml_proc I want to pass
a SP and a parameter. Something like EXEC sp_run_xml_proc 'get_xml_LAC',
@.parameter. This @.parameter should be used as a parameter to get_xml_LAC
stored proc. Is it possible with 'ExecuteWithResultsAndMessages2'? If yes,
then can you direct me how I can fix it? BOL hasn't been much help.
EXEC @.hr = sp_OAMethod @.object, 'ExecuteWithResultsAndMessages2',
@.results OUT, @.procname, @.msgs OUT, @.parameter
IF @.hr <> 0 BEGIN
EXEC sp_displayoaerrorinfo @.object, @.hr
RETURN
ENDHi
Have you tried adding the value to the procedure name?
set @.procname = @.procname + ' ' + @.parameter
John
"S" wrote:

> Here is a code snippet that I am using from Ken's sp_run_xml_proc.
> I want to modify it, such that when I invoke sp_run_xml_proc I want to pas
s
> a SP and a parameter. Something like EXEC sp_run_xml_proc 'get_xml_LAC',
> @.parameter. This @.parameter should be used as a parameter to get_xml_LAC
> stored proc. Is it possible with 'ExecuteWithResultsAndMessages2'? If yes,
> then can you direct me how I can fix it? BOL hasn't been much help.
> EXEC @.hr = sp_OAMethod @.object, 'ExecuteWithResultsAndMessages2',
> @.results OUT, @.procname, @.msgs OUT, @.parameter
> IF @.hr <> 0 BEGIN
> EXEC sp_displayoaerrorinfo @.object, @.hr
> RETURN
> END|||Worked like a charm. Appreciate it.
"John Bell" wrote:
> Hi
> Have you tried adding the value to the procedure name?
> set @.procname = @.procname + ' ' + @.parameter
> John
> "S" wrote:
>