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.

  • 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
    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. 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
    Feb 12 at 22:53


Your Answer

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