Cascading precedence constraints SSIS

Question

I have a question on how to set up precedence constraints in SSIS.

I have the following flow in the package:

• The execute SQL task returns a string value in this format '1111' that is then stored in the variable called "data"
• The point of it all is to control which script tasks gets to execute For example, if the value is "1111" then all 4 scripts get to run. If the value is "1011" then scripts 1,3,4 get to run... you get the picture
• The scripts DEPEND on the previous one in all cases.
• The constraints evaluate with an expression such as this: SUBSTRING(@[User::data], 2,1)=="1" for script 2, for example.

The problem: If one of the scripts dont run, then the next one wont either (because the constraint never got to the evaluated). For example, for data = "1011", the scripts 3 and 4 never get to run because number 2 never ran...

Do you know a better way to make this work?

Using SQL Server 2008 with BIDS

Answer 1

I agree with @Iamdave for his direction that says modify your script in each script do the check there if it should or should not execute rather than using expression constraints.

However, because someone might want to do this with Data Flow Tasks or something here is a way to do it with SSIS components. Your problem is that you want to conditionally execute a task but whether or not that task gets executed you then want to conditionally execute another task. By placing each script task in a Sequence Container the precedence between sequence containers will always make the other container execute as long as that prior container does not fail or does not have a expression as a constraint. But once in the container you need to be able to set a Conditional Precedence. You can do that by adding a dummy task of some kind ahead of the script task and adding the constraint on that precedence.