notification after SQL send port done with putting the data into database

Question

I have 6 send port.......once it's done putting the data into all the 6 table...

i want to take one data from one table and update it on all the other 5 table.....

i have written stored procedure for that...

but i want to do it only once its completed its process.....

is there anyway for that..

I want ot run my stored procedure only once its done putting the data in all the tables...

for that how to know ok my send port is done with insert statement.....now i can run my stored procedure...

its a big file..its take around 2 hour in putting the data into database...but i want to automate the process...

Answer 1

What you need is probably a simple orchestration to handle this process.

The orchestration could be activated by receiveing the incoming file and send that to all the send ports. It will then wait for a response for each port. Once all the responses has been received you can move forward and fire of you final SP to read and update the last table.

Answer 2

I can see two potential solutions:

1. The simple and slightly convoluted approach:

A 'data-insert' orchestration uses the Parallel Actions shape (with six separate actions) to send insert-grams to the database in question (or possibly a single composite insert-gram - but I can't comment on the parallelism). Once each insert action is complete, a subsequent sequential action updates a control table to mark the data as 'successfully inserted'.

A separate orchestration polls the control table waiting for each 'successfully inserted' record to be inserted; once there are six, invoke your stored procedure.

2. The complex yet elegant approach:

Use a scatter and gather pattern (Google 'BizTalk Scatter Gather Pattern'), with a master orchestration receiving a response as the data is inserted (this will give you true parallelism). Once all scatter orchestrations have completed, invoke your stored procedure.