I want to run two or more procedures in parallel

Question

I have list of procedures. All procedures are not dependent upon each other. So, I need to do is, to run the independent procedures in parallel. I have 4 procedures that are to be run parallel. When the procedures are run successfully, now I need to go to the next task. These procedures create about 10 tables.

Next task is to execute the set of procedures. I have made one table, where I describe the dependency of these procedures to the tables created above. After any one of the above procedures is completed, I should come to this set of procedures, and find out those procedures whose dependency tables are already created. If any procedure whose dependent tables creation is completed, I need to execute this procedure.

Running 4 procedures parallel is done by dts. But, difficulty for me is to transfer the task from the above 4 procedures to the below set of procedures. Please help me to complete my task.

Thanks in advance

Answer 1

Which version of Sql server? If you are on 2000, try using DTS and If you are on 2005, try using SSIS.

Answer 2

In a nut shell, you can't.

The DTS/SSIS task will wait for all 4 procs to complete: you can't asynchronously continue one thread/connection in a DTS/SSIS task that runs parallel

I'd consider using a wrapper stored proc to run one of the 1st 4, test for tables, launch other stored procs. This way the DTS/SSIS task can launch 4 wrappers in one go.

Or maintain 4 execution paths through an SSIS package so each "thread" runs at it's own pace.