This is the 15th day of my participation in the November Gwen Challenge. Check out the event details: The last Gwen Challenge 2021

Translation reference

Advanced SSIS Workflow Management – Level 8 of The Stairway to – The Stairway to Integration Services Integration Services aims to have a comprehensive and clear understanding of SSIS. All contents are carried out on the basis of the original text. Due to various reasons such as version differences and personal doubts, the principle of complete translation is not adopted. Hopefully eventually it will be more conducive to learning and understanding SSIS,

Thanks for your support!

Advanced SSIS workflow management

In the previous section, we created a new SSIS package and first looked at the script tasks and priority constraints in SSIS. The MaxConcurrentExecutables package property was checked. The “On Success”, “On Completion”, and “On Failure” features of the priority constraints were also checked, demonstrated, and tested.

In this article, take an in-depth look at SSIS workflow management — learning about SSIS variables and complex priority constraint expressions.

About Variables

Open the precedence. DTSX package (if it is not open). Click on the SSIS drop-down menu at the top of Visual Studio and select the “Variables” menu item to open the show Variables window.

At the top of the Variables window, you can find the SSIS Variables toolbar. These buttons are:

  • Add a variable
  • Mobile variable
  • To delete a variable
  • The grid option

In the Grid option, you can set whether to display system variables and the columns displayed in the table in the variables window.

System variables are variables in the System namespace. Many SSIS package properties, or components and object properties created in SSIS packages, are system variables.

Variables and Namespaces

There are two default namespaces in the SSIS package: System and User. We cannot add system variables, but we can add user variables or create a new namespace. Variable names under scope and namespace must be unique.

For example, in the “User” Namespace you can have a “MyVariable” VariableName, fully qualified as “User::MyVariable”(

::

). In the current example, User::MyVariable is in the precedence. DTSX package scope. We can have another variable “MyVariable” in the “User2” namespace, User2::MyVariable is also in the Precedence. DTSX package scope.

To create the User2 namespace, you need to display the namespace column and edit the “User” text to “User2”. When accessing variables with the same name in different namespaces in the same scope, you need to use fully qualified variable names, otherwise you will receive an error similar to the following:

The variable name is ambiguous because multiple variables with that name exist in different namespaces. Specify namespace qualified names to prevent ambiguity.

The variable name is ambiguous because multiple variables with this name exist in different namespaces. Specify namespace-qualified name to prevent ambiguity.

Add a Variable

Click the “Add Variable” button in the Variables window and a new Variable named “Variable” of the Int32 data type is created.

Rename the variable to “MyBool” and change its data type to Boolean.

You can now use this variable in a precedence constraint expression.

Expressions and precedence constraints

Right click the priority constraint between “Script Task 1” and “Script Task 2” and click “Edit…” To open the priority constraint editor

There are two sets of control buttons called “Constraint Options” and “Multiple constraints.” When building the SSIS package, I directed the control flow tasks to be executed in a top-down order.

A positive side effect of Positioning tasks is that execution flows from top to bottom. The arrangement of the Groupboxes on the priority Constraint editor is somewhat consistent with the physical layout of the priority Constraint on the control flow: the “Constraint Options” box sets the attributes in the priority Constraint related to the previous task or priority Constraint “starting point”; The Multiple Constraints box sets the properties for the next task or priority constraint “endpoint”.

In the Priority constraint editor, click the Evaluation Operation drop-down list. You can see a list of options:

  • The constraintConstraint
  • expressionExpression
  • Expressions and constraintsExpression and Constraint
  • Expression or constraintExpression or Constraint

The default option is constraint (the previous introduction used this option). Constraints allow you to configure when or if the next task is executed — just based on the results of the last task. The value drop-down list contains options for constraint evaluation: success, failure, and completion.

Expressions are used in precedence constraints

Change the “Evaluation Operation” drop-down list to “Expression” and enter “@mybool” in the Expression text box

Verify the expression in the expression text box by clicking the Test button.

The SSIS variable MyBool is a Boolean value with a default value of False. The value in the expression text box must be True or False. You can also edit the expression to “@mybool == True”. The expressions “@mybool” and “@mybool == True” are logically equal because they have the same result.

When configured, close the priority constraint editor.

Debugging runs the ‘precedence. DTSX’ package. Running “Script Task 1” displays a success or failure message box. At this point, it doesn’t matter which option you choose, because the priority constraint evaluation is based solely on the value of the SSIS Boolean variable @mybool. @mybool defaults to False, so “Script Task 2” will never execute (because precedence constraints can never be evaluated to True)

Variables are used in scripts for scripting tasks

Let’s create a more useful test.

Open the “Script Task 1” editor and click the ellipses of the “ReadWriteVariables” property to display the variable selection window. Select the User::MyBool variable.

Click OK to close the variable selection window. The ReadWriteVariables property of the script Task editor now contains the SSIS variable “User::MyBool”.

Click the “Edit Script” button and Edit the code in Main as follows:

public void Main()
{
        // TODO: Add your code here
        var sTaskName = Dts.Variables["TaskName"].Value.ToString();
        var boxRes = MessageBox.Show("Set MyBool to True?, sTaskName, MessageBoxButtons.YesNo);
        if (boxRes == DialogResult.Yes)
        {
            Dts.Variables["User::MyBool"].Value = true;
        }
        else
        {
            Dts.Variables["User::MyBool"].Value = false;
        }
        Dts.TaskResult = (int)ScriptResults.Success;
}
Copy the code

Save and close the script editor, and click OK to close the script task editor.

DTSX package to test different buttons in the message box of “Script Task 1” : “Script Task 2” will be executed when clicking “Yes”; When you click “No”, the entire execution ends without executing “Script Task 2”.

Use variables in depth

Right click on “Script Task 2”, click Copy, then right click on a blank part of the control flow and click on “Paste”. The Script Task 2 1 will appear.

Rename “Script Task 21 “to “Script Task 3″, concatenate a new” priority constraint “from “Script Task 1”.

If the SSIS package is executed at this time, “Script Task 1” will execute successfully regardless of the MyBool variable you clicked on, and the priority constraint connecting “Script Task 1” and “Script Task 3” will be evaluated as True. The message box “Script Task 3 Completed “will be displayed.

Right-click the limited constraint between “Script Task 1” and “Script Task 3”, open the priority constraint editor, and change the “Evaluation Operation” to “expression”. In the expression text box type “! @mybool “, as follows:

! “” @mybool means “Not MyBool”. Click ok to close the editor. The current control flow should be similar to the one below

Execute SSIS package, if you click “Yes” button of “Script Task 1” prompt box, “Script Task 2” will execute; If you click the “No” button, “Script Task 3” will execute

The original article has an introduction to visual feedback for color preference constraints. In fact, in the Visual Studio 2010 shell(SQL Server 2012), Visual feedback does not play a sufficient role because the color does not change in the corresponding state. This may be different from the version, so omit this paragraph.

Copy and disable to preserve existing work

Let’s keep the work we’ve done until we do more.

From the SSIS Toolbox, drag and drop the Sequence Container into the control flow.

The display of SSIS toolbox can be controlled by the menu item “SSIS Toolbox “in the top menu SSIS

Click on a blank area of the control flow and draw a box around the three script tasks that you can drag as a group into the sequence container

After dragging in, most of the time, the sequence container will automatically resize around these items. If not, you can adjust the size manually.

Right-click the sequence container and click “Copy”. Then, right – click the control flow blank and click “Paste”. The control flow should look like this:

Right click on the first sequence container (the original one) and click Disable. The sequence container and its contents appear as disabled (grayed)

Now, when we do new work, we can keep what we’ve already done.

Multiple constraints (Multiple Constraints)

In “Sequence container 1”, delete “Script Task 1”. The precedence constraint between “Script Task 1” and “Script Task 2″/”Script Task 3” is also removed. Copy “Script Task 2” and paste it into “sequence container 1” and rename the new Script Task to “Script Task 4”.

Move and adjust “Script Task 2” and “Script Task 4” side by side, A priority constraint is connected between “Script Task 2” and “Script Task 3”, “Script Task 4” and “Script Task 3”, respectively

Now, consider: What must be done in order for “Script Task 3” to execute?

  1. Do nothing. “Script Task 3” executes when other tasks are executed.
  2. “Script Task 3” executes after “Script Task 4” or “Script Task 2” executes and succeeds.
  3. “Script Task 3” is executed after “Script Task 4” and “Script Task 2” are executed and successful.
  4. “Script Task 3” is never executed.

You can find out by examining two priority constraint editors, focusing on the “multiple constraints” group box:

Remember, the multiple constraint box group defines how the priority constraint works at the EndPoint (the arrow at which the priority constraint ends). When only one priority constraint on the endpoint falls on the task, these options are meaningless (they behave in the same way). However, these options are critical when more than one constraint falls on the endpoint task (shown here).

The Logical AND option is selected by default. Means that all priority constraints located on an endpoint task must be evaluated before the endpoint task executes. In this case, it means that “Script Task 4” and “Script Task 2” must end and succeed before “Script Task 3” can fire. Execute the SSIS package for validation.

Script Task 3 will not execute until “Script Task 4” and “Script Task 2” have completed their execution and succeeded. It’s a function of logic and multiple constraints.

Let’s test for Logical Or. Stop the debug run, double-click on one of the enabled priority constraints to open the editor, and change the multiple constraints to “Logical or”. Click OK to close the priority constraint editor. The two priority constraints connected to “Script Task 3” become dotted lines.

Because multiple constraints deal with endpoint tasks. A change to one is applied to the precedence constraints of all tasks connected to the endpoint.

Previously, multiple constraints were configured as “logical and”, and the previous priority constraints must all be evaluated before subsequent tasks are performed.

Now configured as “logical or”, what does “Script Task 3” do before execution? Here is answer 2 above: “Script Task 3” executes after “Script Task 4” or “Script Task 2” executes and succeeds.

Execute the SSIS package and test it.

Mixed constraint and expression evaluation operations (expression evaluation operations)

Stop SSIS if it is still running.

Double-click the priority constraint between “Script Task 4” and “Script Task 3” to open the priority constraint editor. Change multiple constraints to logical and. Set Evaluation Operation to “Expression and Constraint”. Make sure “value” is set to “success” and the expression is set to “@mybool”

For this priority constraint to be evaluated, the previous task must be executed successfully and the SSIS variable “User::MyBool” must have a value of True. MyBool” default is False.

This precedence constraint will never be evaluated to True because the constraint must be True(execution Success, Success) and the expression (MyBool) must be True. But MyBool is False. This ultimately prevents the evaluation of priority constraints, regardless of whether the script task executes successfully or not.

Try executing the current package and find that “Script Task 3” will never execute.

Reopen the priority constraint editor between “Script Task 4” and “Script Task 3”. Change “evaluation” to “Expression or Constraint” :

Close the editor and run the SSIS package. This time “Script Task 3” will execute.

“Script Task 3” executes because both priority constraints connected to it are evaluated as True. It is easy to understand how to evaluate the priority constraints between “Script Task 2” and “Script Task 3”. But how do you evaluate the priority constraints between “Script Task 4” and “Script Task 3”? The evaluation operation is set to “expression or constraint,” which means that the expression or constraint must be evaluated to True. If both are True, the precedence constraint will also be True. But the OR condition requires at least one expression OR constraint to evaluate True. Because the variable value is set to False, the expression MyBool does not result in True. Constraint — successfully executed the previous task — evaluates to True. This is why “Script Task 3” is allowed to be executed.

Priority constraint annotations

During the operation, you may notice that when we changed “evaluation operations” from “expressions and constraints” to “expressions or constraints”, there was no visual change in the rendering of the drawing in the control flow. (In fact, evaluation only changes color visually when it changes the value of the constraint.)

How can we tell the difference? This can be done with annotations: click on the priority constraint to select it, then press “F4” to display the attribute. You can see the first attribute “ShowAnnotation”, which is set to “ConstraintOptions”.

The ConstraintOptions setting of the ShowAnnotation attribute shows: the text for the evaluation operation (evaluation operation) that is used to clarify the priority constraint.

Comprehensive use of

Edit Main() for “Script Task 2” and “Script Task 4” as follows:

public void Main()
{
         // TODO: Add your code here
         var sTaskName = Dts.Variables["TaskName"].Value.ToString();
         var boxRes = MessageBox.Show("set "+sTaskName + " Succeed?"."Confirm", MessageBoxButtons.YesNo);
         if (boxRes == DialogResult.Yes)
         {
             Dts.TaskResult = (int)ScriptResults.Success;
         }
         else
         {
             Dts.TaskResult = (int)ScriptResults.Failure; }}Copy the code

This code creates a task success prompt box within each script task. Edit the priority Constraint between “Script Task 4” and “Script Task 3” and set the evaluation operation to “Expression and Constraint”, value to “failure” and Expression to “@mybool”

Edit the precedence constraint between “Script Task 2” and “Script Task 3” and set the evaluation operation to “Expression” and the Expression to “! @MyBool”

Here is what happens when the “ShowAnnotation” attribute for both of the priority constraints is “ConstraintOptions” :

Setting the Evaluation Operation to an expression causes “ConstraintOptions” to appear as “Completion and “.

What happens before “Script Task 3” is executed?

  1. Nothing happens. “Script Task 3” will execute when other tasks execute.
  2. If “Script Task 4” or “Script Task 2” is executed and successful, “Script Task 3” is executed.
  3. When “Script Task 4” and “Script Task 2” are executed and successful, “Script Task 3” is executed.
  4. “Script Task 3” will never execute.

You can run the SSIS package under test.

This is a trick question. The answer is 4 — “Script Task 3” will never execute.

Alter “MyBool” to True, change the expression between “Script Task 2” and “Script Task 3” to “@mybool”

Other traps (Gotcha)

Disable “Script Task 4”, when executing SSIS package, “Script Task 4” will not execute, and “Script Task 3” will not execute either

Why is that? MyBool is True, “Script Task 2″ is complete. Script Task 4” skip. When “Script Task 4” is disabled, the control flow is inferred to be “Success”. This means that the failure-first constraint will not be evaluated.

conclusion

In this article, we evaluate and detect multiple constraints using SSIS variable control priority constraints, and look at a “gotchas” note.