Implementing stored procudure

[major]

Hi all,

I have a below requirement , can you please suggest me how best to achieve this

1.I have one oracle table which will contain some columns (daily refresh)
2.I need to read the values from the the above table and pass them to an SP.
3 Need to execute the sp with those values as params.
4.Get the output of the sp into a sequential file.
5.Ftp the file.

I am passing the values of table in point 1 to a file and then to an sp as input params , but I'm not sure how to "capture" the output of the stored procdure.

Thanks in advance

[major]

Any suggestions please....

[chulett]

I don't have any docs in front of me, so this is off the top of my head...

From what I recall, the Stored Procedure stage has a property to set it to function as a Source, Target or... Transformer? Basically meant to be in the job flow and get called for every record through the job. I would try setting it like that and then define / map your output columns on the link going out from the stage. From there you can do whatever you like with them - land, ftp, etc.

[asorrell]

Craig is correct. In this case you'd want to set the stored procedure to be a "transformer" type and set it to return multiple rows.

In your job you'd do a read from the Oracle table to get the information you need - and pass that to input parameters on the stored procedure.

[major]

Thanks Guys,

Using STP stage as 'transform' solved my problem :D