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.
Looping through stages?
Moderators: chulett, rschirm, roy
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?
You sure you don't need to call it 4 times, once after all rows are processed?
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Premium Member
- Posts: 425
- Joined: Sat Nov 19, 2005 9:26 am
- Location: New York City
- Contact:
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
-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
Julio Rodriguez
ETL Developer by choice
"Sure we have lots of reasons for being rude - But no excuses
ETL Developer by choice
"Sure we have lots of reasons for being rude - But no excuses