0

In the example I pasted in the image, I have 5 Tasks. I don't think it's important what kind of Tasks I'm using--but I could be wrong, of course--because the main issue seems to be how the constraints (the arrows) flow.

I have one flow that goes: Task 1, Task 2, Task 3, Task 4, Task 5. The other flow goes: Task 1, Task 2, Task 5.

What I'm trying to do is: If Task 2 fails, skip Task 3 and 4 and go right to Task 5. If Task 2 succeeds, go ahead and run Tasks 3, 4, and 5.

Unfortunately, when I debug the package, the package effectively hangs and never executes Task 5, regardless if the Task 2 Constraint is Success or Failure:

enter image description here

Is there some design limitation where you cannot assign multiple constraints, pointing to the same Task?

I'd rather not have boiler plate code, where Task 5 is created twice, one for each flow. Sure, in this example, it's only one Task. I could duplicate Task 5 and the package should run.

enter image description here

But for scaling, it's woefully inefficient and a change management nightmare to create n number of duplicate Tasks, just to skip two Tasks.

How can I make this work as the flow scales? Is it possible for a Task to have multiple constraints pointing at it and I'm just not setting something correctly? If not, what are my options? Could using a container help me out here?

Thank you for your help in advance.

3
  • 1
    Some guidance here sqlshack.com/overview-of-ssis-precedence-constraints Essentially you should be able to right-click on those different connectors and set the various properties of the constraints - including AND/OR conditions, which is probably what you need for your case where one of the preceding tasks may either fail OR succeed (because, of course, the execution result cannot be both failure AND success)
    – Craig
    Commented Feb 9 at 2:56
  • Unfortunately, that's not working. I tried various permutations of constraints and expressions and that's not the issue. If you test out my example above, you'll see it chooses one path (success) or the other (failure) but still hangs because of two arrows pointing to the same task. That's the crux of the issue here. It's not that it's choosing both paths. It's that two paths are connected to one task. And for some reason, SSIS is not liking that. Commented Feb 9 at 15:07
  • Sorry, I don't really have time for testing it myself, and I don't use SSIS all that frequently. Hopefully someone else who's a more regular user - and has dealt with a similar situation - can assist
    – Craig
    Commented Feb 12 at 22:53

2 Answers 2

0

https://learn.microsoft.com/en-us/sql/integration-services/control-flow/integration-services-tasks?view=sql-server-ver16#types-of-tasks

Type of task: Custom Tasks.

Configuration of Task section explains part of your issue for run-time events.

Also check out: https://learn.microsoft.com/en-us/sql/integration-services/control-flow/add-or-delete-a-task-or-a-container-in-a-control-flow?view=sql-server-ver16

It does state you can use multiple tasks.

Have you tried not adding another duplicate task and just connecting the arrows to each task for workflow containers? The options provided use: https://learn.microsoft.com/en-us/sql/integration-services/control-flow/for-loop-container?view=sql-server-ver16

For loops to configure what you want to specify here.

0

The problem is not that you have two arrows pointing to the same task. You absolutely can have two arrows pointing to the same task. The problem is that your setup requires a logical self-contradiction, as for Task 5 to run, Task 2 would have to both succeed (for the path through Tasks 3 and 4) and fail (for the direct path).

Craig is correct that you need to adjust the constraints. This is the final item covered at https://www.sqlshack.com/overview-of-ssis-precedence-constraints/, being AND/OR logic for multiple arrows.

You should right-click on the Failure arrow, choose Edit, and make the change from "Logical AND" to "Logical OR". This will also automatically adjust the arrow coming out of Task 4 to a dashed line.

Before: package never runs Task 5

On Task 2 pass:

1

On Task 2 fail:

2

After: Task 5 runs whether Task 2 succeeds or fails

On Task 2 pass:

3

On Task 2 fail:

4

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.