Use of precedence constraint in the SSIS series of Microsoft bi

Precedence constraint priority constraint-used in the control flow to link various tasks and iner in the control flow, and the associated task or container can be executed only when certain conditions are met. For example, the first execute SQL task is called precedence-executable, and the script task is called constrained-executable because it is downstream of the associated arrow. The upstream task of the associated arrow is executed first. Whether the constrained executable task under the associated arrow can be executed depends on the precedence constraint configuration. Configuration in est_select_value-the query result is assigned to a variable of the string type. The script task only displays the value of this variable. Double-click the Green Line. By default, you can see the following configuration, including the constraints and multi-constraints options and configuration.

1. Evlaution operation = constraint determines whether to execute downstream script task tasks based on the execution results of upstream tasks (subject to execution results)
2. Value = success: the upstream task est_select_value is executed successfully before the downstream script task is executed.

After the execution of est_select_value is successful, sce_show_resultvalue = failure upstream task est_select_value is automatically executed. After the execution fails, the downstream script task is executed to create a scr_show_error script task, which does not perform any operations. Change the connection line between est_select_value and scr_show_error to failure. Change the SQL statement in est_select_value to select 1/0 as A. In this case, an error with Zero Divisor occurs, but scr_show_error can be executed. Value = complete upstream task est_select_value no matter whether the execution is successful or fails, as long as the execution is complete, execute the downstream script task to create a new script task-scr_show_complete without any operation in it. Modify the connection line to complete. When the SQL statement 'select 1/0 as a est_select_value 'fails to be executed, it is executed. When the SQL statement select 1 as a est_select_value is successfully executed, it is also executed. Evlaution operation = expression is subject to expression constraints and is irrelevant to the result of successful execution of upstream tasks. Two script tasks-scr_show_exp_empty and scr_show_exp_value are added. Constraint configuration of scr_show_exp_empty-constraint configuration in scr_show_exp_value-when SQL in est_select_value is select 1 as a, the value of pv_result is 1, and the execution of est_select_value is successful! When the SQL statement in est_select_value is select 1/0 as A, the value of pv_result cannot be normally accepted from est_select_value. The default value "" is used, and the execution of est_select_value fails! It can be seen that whether or not scr_show_result, scr_show_error, and scr_show_complete are executed depends only on the execution result of est_select_value (success, failure, complete), but not on the expression. The execution of scr_show_exp_empty and scr_show_exp_value is only related to the expression, but not the execution result of est_select_value. The execution result and expression and the execution result or expression add four new scripts taskscr_show_success _ & _ Empty. The configuration must be consistent with the constraint. The configuration in scr_show_success_or_empty requires that expression and constraint be valid. The configuration in scr_show_faliure _ & _ value requires that expression and constraint are both valid. The configuration in scr_show_faliure_or_value requires that expression and constraint be valid. When the SQL statement in est_select_value is select 1/0 as A, the execution status is failure, and pv_result is an empty string. Therefore, src_show_success_or_empty satisfies the pv_result = "" condition, while scr_show_failure_or_value satisfies the execution result = failure. When the SQL statement in est_select_value is select 1 as a, the execution state is success and pv_result is 1. Therefore, src_show_success_or_empty satisfies the execution state = success condition, while scr_show_failure_or_value satisfies pv_result! = "". When multiple constraints exist, scr_all_true is affected by both est_select_value_1 and est_select_value_2. By default, the two priority constraints of scr_all_true are shown in. This multi-constraint means that. From est_select_value_1 to scr_all_true, only one result is true or false. The true or false result is obtained by the final result of the constraint/expression/constraint and expression/constraint or expression in est_select_value_1. Similarly, est_select_value_2 can obtain a final calculation result-true or false. That is to say, there are multiple condition constraints that affect scr_all_true. How can we achieve the two constraints to act on scr_all_true together? It is determined by the logical and or logical or options in the following multiple constraints. Test Condition 1

1. Est_select_value_1-SQL = select 1 as
2. Est_select_value_2-SQL = select 1 as
3. Logical and (although it is two constraints, but change one of them, the other will be changed automatically, that is, it must be both logical and or logical or ).

Test condition 2

1. Est_select_value_1-SQL = select 1 as
2. Est_select_value_2-SQL = select 1/0 as
3. Logical and

Test Condition 3

1. Est_select_value_1-SQL = select 1 as
2. Est_select_value_2-SQL = select 1/0 as
3. Logical or

The line changes to a dotted line at the same time. Execution is successful! Test Condition 4

1. Est_select_value_1-SQL = select 1 as
2. Est_select_value_2-SQL = select 1/0 as a (constraint-completion)
3. Logical or

The line changes to a dotted line at the same time. Execution is successful! Test condition 5

1. Est_select_value_1-SQL = select 1 as
2. Est_select_value_2-SQL = select 1/0 as a (constraint-completion)
3. Logical and execution successful!

To better understand the content of the conditional expressions and constraints, we can set the attributes of the constraint line to display the information, rather than manually commenting. Select the constraint line, modify its showannotation attribute, and change asneeded to constraintoptions. In this way, we can clearly see the State judgment and expression judgment in the constraints! The summary above is the full content of precedence constraint! Although the content is quite large, these configuration conditions are met in different situations. For example, a file archiving task has such constraints. One is to archive data as long as the previous data is loaded successfully, and the other is to perform the archive operation no matter whether the previous data is loaded successfully or not.

For more bi articles, see the Bi series Essays (SSIS, SSRS, SSAs, MDX, SQL Server, to help others quickly see these articles in the recommendation bar of the biwork blog.

Use of precedence constraint in the SSIS series of Microsoft bi