Okay, lets see how I can phrase this:
We have 42 stored procedures that are executed as part of our weekly processing. Currently, they are executed in a DTS package on 6 connections (threads). Each procedure is in an Execute SQL task, and there are from 4 to 10 tasks on a connection, thus there are a total of 42 Execute SQL tasks in the package.
There is no rhyme or reason as to how long each procedure takes to run. One can take 20 minutes last week and 12 minutes this week, another can run in 40 minutes last week and 60 minutes this week. I have made multiple attempts to balance these out in the package, but to no avail. What happens is that 2 of the threads are complete (have executed each of the Execute SQL statements in the workflow), and the remaining four are running tasks for another 45 minutes.
What I have in mind is to redo the package.
I have built a table that contains the names of the 42 procedures. It also contains a bit column, indicating the procedure name has been retrieved. A stored procedure will retrieve the name of the next available procedure and set the bit. I have built an ActiveX task that will open an ADO connection, run the proc and return a procedure name to execute. I figure I can dynamically set the SQL statement in the EXECUTE SQL task.
I have created 6 SQL Server connections in the package. What I need to do is, if a connecction is available, run the ActiveX task to get a procedure name. If the value returned is not an empty string, dynamically set the SQL statement in the SQL task and run it on the open connection. Keep doing this until I have a task running on all 6 connections, and when a the Execute SQL task has finished, loop back to the ActiveX task, get the next procedure name, and execute it. Keep doing this on each connection until all 6 have cqalled the ActiveX task and it returns an empty string. At that time, the package should cease execution.
In effect, what I want in the package are 6 connections, 6 Execute SQL tasks, and the ActiveX task. I can add aditional tasks if needed to support the control/looping.
Questions:
1) How do I set up the looping from a trhead to a common ActiveX script, and how does the script know which Execute SQL task to set for the next execution and flow to it?
2) How do I initially set the task for each of the 6 connections? I envision that each connection has an Execute SQL task associated with it.
3) How do I 'terminate' processing on that connection (where do I branch to) once all procedures have been (or are) executing?
Is what I am wanting to do even within the scope of SQL Server 2000 DTS?
