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

Execute parameterized select statement from data flow

I have following requirement. From OLE-DB source I am getting IDS. Then lookup with some master data. Now I have only matching IDs. Now I need find some filed(say Frequency from some table for each above id). I already write stored procedure for same where I am passing ID as parameter.Which is working fine when I run it SQL server management studio.

Query is sort of

Select field1,fiel2... from table 1 where id = @.id

@.id is each ID from lookup

Now I want to call this stored procedure in Data flow. I tried it using OLE DB command but it did not return output of stored procudre. I am getting output same what ever I am passing input.

Is there way to do this? In short my requirement is execute parametrized select statement using data flow trasformation component.

Hi Dnyandeo,

I had a similar problem when trying to implement a parameterized sql command. Firstly i used the execute SQL task.

It appears that you can only use named parameters with an ADO.Net connection. Using the OLEDB connection you have to use Ordinal position numbering.
I found this article which has some good stuff on parameterized SQL commands and helped me a lot.

http://www.juliankuiters.id.au/index.php?topic=dts-2005-integration

I hope this helps.

Cheers,

Grant|||how is your ole db transformation configured?|||

Thanks for reply..BUT I am getting input parametr from
conditional splits in data flow so I want to execute stored

procedue in data flow .
Thanks for reply..BUT I am getting input parametr from
conditional splits in data flow so I want to execute stored

procedue in data flow .

|||

dnyandeo wrote:

Thanks for reply..BUT I am getting input parametr from
conditional splits in data flow so I want to execute stored

procedue in data flow .
Thanks for reply..BUT I am getting input parametr from
conditional splits in data flow so I want to execute stored

procedue in data flow .

again, how is your ole db transformation configured?|||

OLE DB command trasformation is configure as below.

I am calling stored prodedure by exec statement with parameters and parametrs are mapped with incoming data. Is any thing more required?

|||

dnyandeo wrote:

OLE DB command trasformation is configure as below.

I am calling stored prodedure by exec statement with parameters and parametrs are mapped with incoming data. Is any thing more required?

it would difficult to assist you without knowing your exact t-sql code, you exact exec statement, and exactly how you're mapping the parameters.|||I am executing it

exec CSP_GetJJKKChangedPricingFrequency @.BP_ID

@.BP_ID is matched with BP_IF field of resultset getting from Derived column control.

this is code is stored procedure

select VJJKK.BP_ID,

VJJKK.Security_ID,

VJJKK.CUSIP_Value,

VJJKK.State_Code,

VJJKK.Coupon_Rate,

VJJKK.Maturity_Date,

VJJKK.Pricing_Frequency,

VJJKK.Security_Desc,

VJJKK.BP_ID_Status

from view_InterfaceJJKK80OUT VJJKK

where

VJJKK.Pricing_Frequency Not in( select distinct Pricing_Frequency_Code

from

Sch_Interface.JJKennyMaster JJKKM

where JJKKM.BP_ID = @.BP_ID)

|||

dnyandeo wrote:

I am executing it

exec CSP_GetJJKKChangedPricingFrequency @.BP_ID

@.BP_ID is matched with BP_IF field of resultset getting from Derived column control.

this is code is stored procedure

select VJJKK.BP_ID,

VJJKK.Security_ID,

VJJKK.CUSIP_Value,

VJJKK.State_Code,

VJJKK.Coupon_Rate,

VJJKK.Maturity_Date,

VJJKK.Pricing_Frequency,

VJJKK.Security_Desc,

VJJKK.BP_ID_Status

from view_InterfaceJJKK80OUT VJJKK

where

VJJKK.Pricing_Frequency Not in( select distinct Pricing_Frequency_Code

from

Sch_Interface.JJKennyMaster JJKKM

where JJKKM.BP_ID = @.BP_ID)

if i'm not mistaken, your sql statement in the ole db command should be the following:

exec CSP_GetJJKKChangedPricingFrequency ?

is this what you have as your sql statement?