This is the 14th day of my participation in the November Gwen Challenge. Check out the event details: The last Gwen Challenge 2021
Translation reference
Intermediate SSIS Workflow Management — Level 7 of the Stairway to Intermediate SSIS Workflow Management — Level 7 of the Stairway to 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!
Intermediate SSIS workflow management
In the previous section, we built a new SSIS package, looked at script tasks and priority constraints in SSIS, and examined the MaxConcurrentExecutables package properties.
In this article, we examine, demonstrate, and test the “On Success,” “On Completion,” and “On Failure” functions of the priority constraint.
Constraint Evaluation
The “priority constraint assessment” was mentioned in the previous section. It determines the start of the termination task. The types of assessment fall into the following categories.
On Success
The default type for the priority constraint evaluation is “On Success”.
To demonstrate, run the Precedence package in Visual Studio. First, a message box from “Script Task 1” appears:
When the ok button of the message box is clicked, “Script Task 1” succeeds (the green check box in the upper right corner indicates success), the evaluation between “Script Task 1” and “Script Task 2” is True, then “Script Task 2” executes and displays the following message box
Click the “OK” button of the “Script Task 2” message box and the “Precedence. DTSX “package completes and succeeds.
The priority constraint here is configured as “On Success” evaluation.
Stop the running of the package.
On Completion (Success)
Right-click the priority constraint to see the configuration Options available from the right-click menu. You can find “edit…” Success, Failure and Completion follow.
Click “Finish” and the color of the priority constraint will change from green to (dark) blue. (Different versions seem to have different colors. The successful color was pure green, but now it’s blue-green. Originally finished in blue, it is now a very deep blue, almost black).
The priority constraint between “Script Task 1” and “Script Task 2” is now configured to evaluate “On Completion”. Whether Script Task 1 succeeds or fails, the execution priority constraint is evaluated as soon as it completes and Script Task 2 executes.
Debug runs the SSIS package, and once Script Task 1 is successfully executed, Script Task 2 will execute.
Stop the running of the package.
You might be wondering: “This only tests the precedence constraint On the success of the configuration ‘On Completion’; How do we know to work when we fail?” The answer is: “In this case, we do not know the conditions under which the ‘On Completion’ configuration will fail.”
The following tests are executed On Failure.
On Completion (Failure)
First, we generate a failure condition.
Open the “Script Task 1” editor and click “Edit Script”. Add a judgment to the Main() function to determine whether the message box returns a ‘Script Task’ that succeeds (when the yes button is clicked) or fails (when the yes button is not clicked).
public void Main()
{
// TODO: Add your code here
var sTaskName = Dts.Variables["TaskName"].Value.ToString();
var boxRes = MessageBox.Show(sTaskName + " Succeed?"."Confirm", MessageBoxButtons.YesNo);
if (boxRes == DialogResult.Yes)
{
Dts.TaskResult = (int)ScriptResults.Success;
}
else
{
Dts.TaskResult = (int)ScriptResults.Failure; }}Copy the codeClose the script code editor and click OK to close the script task editor.
To execute the SSIS package, the following message box will appear, click the “Yes” button, and the “On Completion” priority constraint will be evaluated and “Script Task 2” will execute. This is the use case we demonstrated earlier.
If the “No” button is clicked, “Script Task 2” will also execute.
Note: After modifying the Script of “Script Task 1”, you will find that the message of “Script Task 2” is also changed. Click in to edit the script to see, the code is fine.
Solution: Make a random change in the Script of “Script Task 2” (add a space, a carriage return, line feed, etc.) and save it. This will not happen again!
This problem occurs because we copy and paste “Script Task 1” to create “Script Task 2”. Sometimes metadata belonging to a script task being copied will cause code from one script task to be executed in the context of a second script being copied and pasted. It’s rare, but it does happen.
Adding changes will cause SSIS to reevaluate (“re-evaluate”) the Script code embedded in “Script Task 2”. To execute as designed.
So we know that “On Completion” is executed whether the previous task succeeds or fails. Let’s retest “On Success”.
Re-test On Success
Right-click Priority Constraints and set it to Success.
Debug runs SSIS and clicks “No” in the “Script Task 1” message box, causing the Script Task to fail. Then you can see that “Script Task 2” will not execute.
If an exception or error occurs, we use the “On Success” priority constraint to stop execution.
On Failure
To stop the current execution, right – click Priority Constraints and select On Failure. The precedence constraint changes the color to red, indicating that it is configured to evaluate and execute the previous task in the event of failure.
Execute SSIS package, when “Script Task 1” appears, click “No” button, “Script Task 2” will be executed as follows.
You can re-run it and then click yes in the Script Task 1 prompt, “Script Task 1” succeeds, but the priority constraint is not evaluated and “Script Task 2” will not execute.
Stop the current debug run.
conclusion
In this article, we use SSIS scripting tasks to accomplish testing the state of multiple use cases. Use test states to demonstrate how SSIS priority constraints represent the success, completion, and failure conditions of the previous task.