DSXchange

Hello,

I have a requirement to execute a stored procedure after every 1000 rows inserted into the target table. For example after cdc there are a total of 3224 rows to be inserted, I need to execute the stored procedure a total of 3 times and use a value returned from the procedure and insert into the target.
the database I'm using is SQL Server 2008.
Thank you in advance for any help.

Not really a "looping" scenario to me. In your shoes I would probably try splitting off to another link whatever you need to call the procedure and then use a MOD(counter, 1000) function on a count of the records to call it when the function returns 0, leveraging a filter or transformer.

You sure you don't need to call it 4 times, once after all rows are processed?

I would:
-Have a wave generator set to 1,000 after the CDC stage, anywhere before the Target database connector
- Set the commit transaction either to zero, to commit all, or to a 1,000
- Have the call of the store procedure as an after SQL

Good luck

After SQL would typically mean it would only be called once unless the Wave Generator effects that? I assume so, hence the suggestion.

Thank You Craig. This solved my problem