Controlling the flow in SSIS package based on a condition

Question

Is there a way to conditionally (through a script task or anything else), control the flow of program in SSIS?

Currently I have a package that would create 5 different excel sheets (through Execute SQL Task) dynamically. There maybe times when all 5 will have data or only 1 may have data. When its just 1 that has data, it is fine. But the real problem arises when there are 5 DFT's that are trying to write the data simultaneously to the same workbook (albeit different sheets inside that). The package fails with an OLEDB error.

After a lot of head breaking, I finally figured out that it was a concurrency control issue that wasn't allowing me to write to the excel file simultaneously. To further my solution, I used expressions on precedence constraints to control if the sheets get created or not. But the real trouble is that after creating the sheets, the package would fail trying to write data to 2 different sheets simultaneously.

Is there a way, I can assign an 'Execution Order' for the DFT's? This is the reason I am looking for a script task so that when a particular sheet's count is 0 then it does no work and the control moves to another branch.

I hope I have not confused you here. But if I have, I'll be glad to provide more details on this question. Thanks for reading.

Kinna

There is an entire section of documentation about control flow in SSIS, including tutorial examples. Does it answer your question? – Mintun

I doubt that it has anything in there for me. I have already tried my hand at variables and precedence constraints. That doesnt work well. Based on the count returned from a table, I need to control which DFT gets executed 1st and which goes 2nd and so on. – Kinna

Thinking aloud here, but what if you just serialized all the tasks and then where you have your current precedence constraint logic, use that to enable/disable the tasks. I can doctor up that as an answer if you want to see it as a picture. Ought to eliminate the concurrency challenges – Comptroller

Sure thing. Anything that could be of help is much appreciated. – Kinna

Answer 1

My first thought is to have a bunch of sequence containers, one per possible Excel sheet, each of which holds three tasks:

1. A script task to figure out whether or not to create the sheet, and set a boolean package variable accordingly
2. An SQL task to create the worksheet
3. A data flow task to populate the worksheet

The precedence constraint between tasks 1 and 2 would be an expression of the boolean being true:

The precedence constraint between tasks 2 and 3 would be a success constraint, as would the precedence constraints between the sequence containers. Overall, it would look like this:

Instantaneous

This would also eliminate the issue you described in #13976584 since you'd only create the sheet if there was data to put in it. – Instantaneous

Ah yes! This seems to be a very good solution. I had something very similar, but the sequence containers were all linked to a common parent. Something of a "Spiders leg" configuration. I will report back if I face any issues. Thanks for the help. – Kinna

I added all the required pieces and I am glad that its working the way I wanted to to. You help is really appreciated. – Kinna

this works for me when I use the "execute container" in visual studio, but for some reason, fails when I execute a package - any ideas as to why would be welcome as I'm at a loss to understand the difference - execute container it works, but when I execute the package it fails. – Stiff

@codeputer, we will need more details. Are there are any project level params that get invoked or any configuration that gets derived from the package execution? Did you resolve the issue? – Kinna

Answer 2

To make it clear at first sight, in the following example, two conditions are put in a row, they are in the "fx" expression of each SQL task's output arrow. You need to double click each arrow.

Tecu