DSXchange

Hello -
I need to execute to execute a stored procedure that only inserts into a job log then run my etl job. All my input parameters for the stored procedure are job parameters. I am trying to figure out how to execute a stored procedure while passing job parameters and then begin to load the data any help? I have created a stored procedure stage for the sp

thanks

Use an input link. Start from a Transformer, adding a dummy stage variable so it will compile and a constraint of "OUTROWNUM=1" so it only generates one row. Set your job parameters into fields in the input link so they can be easily passed to the stored procedure.

Or you could start with a row generator stage. Set number of rows to produce to 1 and define your columns same as your input parameter.

so your job design will be like

row generator--------------->SP Stage----------------------->Output

Or you could start with a row generator stage. Set number of rows to produce to 1 and define your columns same as your input parameter.

so your job design will be like

row generator--------------->SP Stage----------------------->Output

We did:

Row_Gen-------->Store_Procedure------->Col_Gen--------->Output

Not in a Server job, boys and girls. 'Ware the Job Type.

I was little confused again on how you only get one row to come out of tranformer stage or stored procedure stage based on when complete. Because at the end of the job after the update stored procedure is complete i have to call another stored procedure for job complete

First mention of that requirement. Have you tried (from memory) setting it to a "Transform" type in the SP stage? Rather than Source or Target that is.

i always set it to transform

Swell. Then that means it can support an output link as well as an input link and you can use the Forward row data option to pass the same columns out as came in.

I started with a transformer to the stored procedure but i get an error saying the transformer needs a input link

Because you did not add a stage variable as I specified. No need to use it but it needs to exist.

I was about to compile and insert into the job log so my two questions is this.

1. Since i am only passing one row into the stored procedure, how do i tell the transformer to only pass one row to the stored procedure?

2. After the stored procedue completed how do i tell it to continue on to uploading the data file

I was about to compile and insert into the job log so my two questions is this.

1. Since i am only passing one row into the stored procedure, how do i tell the transformer to only pass one row to the stored procedure?

2. After the stored procedue completed how do i tell it to continue on to uploading the data file

Have you tried @outrownum=1 as a constratint in the transformer??