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?