Search code examples
sql-serverssisssis-2016

SSIS multiple flow constraints re-joining to a single executable not continuing

Firstly, I am new to SSIS and not sure of what format to explain the problem in a repeatable way for testing

So I have a task that loops through a folder looking for flat files and if in the correct format loads them into a SQL server staging table. This seem to work correctly

The next bit is the strange bit. I have a split for success and a split for failure (for user notification etc) on both legs I run a SQL task to count the number of rows in the staging table to process. IF it greater than zero I want to run my SQL stored procedure that handles the load to into the main database table for the data, then clean up.

In my debug email I see the number of rows is greater than 0 but the task does not proceed. I can not understand why. I have tried a number of constrain combinations (On completion, On Success, On Success and RowsToProcess>0). I have also tried removing and adding my SQL Task and remapping. (BTW all my tasks function in SSMS etc)

Nothing seems to work The only thing is that 2 branches re-join at this point, but surely that would not affect it, would it? (see below original screen shots)

Here is my control flow, If I have missed anything please add a comment and I will supply the information if I know it enter image description here

My results from executions enter image description here

On further testing two executables does seem to be the problem! So, additional questions.

  1. Is this expected behaviour that two constraints joining back to a SQL task stops the flow in SSIS (in BPA task centre it does not)?
  2. If, this is the case does this mean you have to repeat code in parallel? (for example if I was to run a clean up script at the end of my flow I would have to write it tice once for success leg and once for failure leg). This seems inefficient, am I missing something?
Solution

  • I think I've found the answer you are looking for here (go give it an upvote)

    I was not aware of it, but basically, it says that bot branches must complete successfully (logical AND) in order for the flow to proceed, which won' happen as the right branch in this case won't even run. The default behavior can be changed in the "constraint properties" (right-click on the arrow that links the blocks) under the "Multiple Constraints" section. That said

    I've never used this method, but that's up to the use case. I would have used "sequence containers" to avoid replicating blocks or multiple constraints, which is not the same but works as long as you don't have to pass data from one block to the other.

    In your case, I'd put all the blocks inside a sequence container except the last 2, and after the container completes execute the last two as they must be always run after the previous block of operations

    note about containers: Containers do not output data but are useful to group operations logically, and you can even run just a container instead of the whole package (from VS).

    Seems today I've learned something new too... Hope this helps you solve your issue