State/Execution ID in SSIS

Question

I have a problem with multiple executions of the same SSIS package. I would like to allow parallel executions each of which handles a subset of data.

So far, I am thinking of using some state variable, but I don't know where to store it.

One option is to use keep the connection open and use temp tables to coordinate the task load. However, temptables cause lots of compilation issues, and they are not maintable.

Are there any other ways to identify the current execution id of the package or scope of execution? I have found no state (either in memory or stored elsewhere) in SSIS so far which I can use to partition/isolate each execution.

Answer 1

So based on my comments above you can try this. I dont know it is quite what your looking for, but maybe it can give you a hint to get further.

I am calling the example with workflowid 1. This is what i mean you can change in your SQL Job agent steps, and then change the parameter on each step, so fx you could add 2 steps executing workflowid 1 and workflowid 4. Then it will only run that sequence container where the constraint is success.

Create a package variable

Create your package flow

Edit your SQL Task Get WorkflowID Add Parametermapping to your package variable

Get the resultset into local variable called WorkflowIDrun

Make your precedence constraints so it only allows one id to pass through Notice: You could add parentworkflowid's so that you can diverse your flow inside the sequence container if you need some of the same logic

End result when package is run with workflowid 1

Create a new SQL Job in your agent. Add the needed steps Notice; I Created two steps for workflowid 1 and 2. Truncate and delete

I then edit my step and correct the variable with the right value. This will be workflowid 1 for truncate and workflowid 2 for delete This could of cause also be in another job you do it, that depends on your needs.