How to control sequence of tasks inside a data flow in SSIS?

2
votes

I am creating an SSIS package, it has one execute SQL task and one data flow task.

In the data flow task, the data is moved from from different source tables to the respective target tables.

Can we control the order in which the ETL execution takes place, so that each table transformation completes before the next one starts?

I don't want to create multiple packages, need to handle it one package.

For e.g., in the data flow task, we have the below code executing:

  • S1->data conversion transf->T1
  • S2->data conversion transf->T2,
  • S3->data conversion transf->T3,
  • S4->data conversion transf->T4,...................

And the data load sequence must be as follows:

  • S1->data conversion transf->T1,
  • S4->data conversion transf->T4,
  • S3->data conversion transf->T3,
  • S2->data conversion transf->T2,...............

Please let me know how to achieve it.

1
ssis

1 Answers

3
votes

I think the best approach to deal with different and constrained data flows is by creating different data flow tasks to handle the requirements of each of them. You could place them in a sequence container using precedence constraints. For example:

enter image description here

You can define the constraints in the way you would like the tasks to complete, being it in regards of the exit status (failure, success or completion) and expression (let's say, you create a variable that controls the precedence constraints), for example:

enter image description here

In this case, the data flow task S2 to T2 will only execute if the value of the variable myvarevaluates to true AND the data flow task S3 to T3 exits with a success status

Hope this helps.