Seventh Integration Services: Intermediate workflow Management

This article is the seventh chapter of the Integration Services series, please refer to the original text for details.

Introduction
In the previous article, we created a new SSIS package, learned about script tasks and precedence constraints in SSIS, and checked the MaxConcurrentExecutables properties of the package.
This article examines, demonstrates, and tests the impact of precedence constraints on workflow when assigned to success, complete, and failed.
constraint Assignment
If you followed the steps in the previous article, open the PRECEDENCE.DTSX package under the My_first_ssis_project solution. Create a precedence constraint in Script Task 1 and Script Task 2.

Figure 7.1 Adding precedence constraints
Quick Review
At the end of this series of sixth articles, I included this important introduction:
The semantics here are very important. The precedence constraint starts in Script Task 1 and terminates in Script Task 2. The start task connects to the starting point of the precedence constraint-there is a semicircle point, which connects to the start task, and the end has an arrow that connects to the terminating task.
The precedence constraint evaluation. They test for one or more conditions, and if the condition is true, the constraint allows the terminating task to begin execution.
Success
To demonstrate, execute the package under the bids debugger. The message dialog box for script Task 1 appears first:

Figure 7.2 Message dialog box for Script Task 1
When you click the OK button in the message dialog box, Script Task 1 succeeds (the background turns green), the precedence constraint evaluates to TRUE, and Script Task 2 executes and displays a message dialog box:

Figure 7.3 Message dialog box for Script Task 2
When you click the OK button in the Script Task 2 message dialog box, the precedence package completes successfully:

Figure 7.4 Precedence Package executed successfully
Complete (Success)
Right-click the precedence constraint to see the available configuration options. At the top is "edit", followed by "Success", "Failed", "done":

Figure 7.5 Configuring constraint Options
If you click Done, the precedence constraint changes from green to blue:

Figure 7.6 "Completion" precedence constraint
The precedence constraint is now configured with the value "done". Regardless of whether Script task 1 succeeds or fails, as long as Script Task 1 completes, the precedence constraint is evaluated, and then Script Task 2 begins execution.
Test the execution package under the SSIS debugger. Once Script Task 1 succeeds, Script Task 2 begins execution:

Figure 7.7 "Finish" value script Task 1 succeeded
This only tests the actions of the finish option when the previous task was successful. If the previous task fails, how does the finish option work?
Complete (failed)
First we need a way to generate errors. Open the Script Task 1 editor and click the Edit Script button. Do you know that the message dialog box can return a value? They return an integer value that represents the type of button pressed and closes the message box. To implement this function, modify the VB code in the public Sub Main ():

 Public SubMain ()DimStaskname as String= Dts.variables ("TaskName"). Value.tostringDimIresponse as Integer=MsgBox("succeed"& Staskname &"?", Msgboxstyle.yesno, Staskname)IfIresponse = Msgboxresult.yes ThenDts.taskresult=scriptresults.successElseDts.taskresult=scriptresults.failureEnd IfEnd Sub

Statement declares an integer iresponse variable and assigns the return value of the MsgBox function to it. The MsgBox function returns a value based on the message box button clicked by the user. The If-else code responds to the value of iresponse, and if the "Yes" button (Msgboxresult.yes) returns successfully, the return fails.
Close the VB Script Editor, and then click the OK button to close the Script Task Editor. Execute the package under the Bdis debugger, and if there is no error, your first message box should:

Figure 7.8 Script Task 1 message box
If you click on the "Yes" button, this completion precedence constraint will be evaluated and then script Task 2 will execute. But this has been tested in the front. If you click the "No" button, Script Task 2 will also execute:

Figure 7.9 Script Task 1 fails with Script Task 2 still executing
If you have been following my actions, it is possible that you will not see a message box like the one in Figure 7.9, instead you will see a message box similar to script Task 1. Why? Remember, we are the Script Task 2 that we get by copying and pasting Script Task 1. Sometimes, too much metadata to identify the copied script task will cause code from one script task to execute in the context of another script task that is copied and pasted. This is a small probability, but it happens occasionally. To achieve the desired result, open the script Task 2 editing scripts to add a line of comments or click. This change causes bids to re-evaluate the scripts in script Task 2. The Script task is then executed as designed.
Now we know that the value of the constraint option is "done", regardless of whether the previous task succeeded or failed. Let's reset the option to "success"!
Success (failure)
Right-click Precedence constraint sets the option value to success, and the constraint changes from blue to green:

Figure 7.10 Constraint option value set to success
F5 executes the package when the prompt message box displays succeed Script Task 1? Clicking the "No" button causes it to fail. 7.11,script Task 2 will not execute:

Figure 7.11 The constraint option is successful and the task after the previous task fails does not execute
We can use the "Success" option to stop execution if an unexpected or error occurs.
Failure (failure + success)
To stop debugging, right-click the precedence constraint and select "Failed":

Figure 7.12 Configuring constraint options
The constraint changes from green to red:

Figure 7.13 Constraint option value set to "failed"
Execute the package under the SSIS debugger when the prompt message box displays succeed Script Task 1? Click the "No" button. Succeed Script Task 2 will execute:

Figure 7.14 Constraint options are "Failed", task execution after previous task failure
Click the Restart button, when the package restarts, Script Task 1 displays a prompt, clicks the "Yes" button, and Script Task 1 executes successfully. The precedence constraint is configured as "failed", the condition evaluates to False, and the Script Task 2 does not execute:

Figure 7.15 The constraint option is "Failed" and the task behind the previous one is not executed
Summary
In this article, we use the SSIS Script task to test various usage cases states. We then used the test states to demonstrate what SSIS precedence constraints respond to Success, completion, and Failure Co Nditions in preceding tasks.
In the next article, we will learn advanced workflow management.

Seventh Integration Services: Intermediate workflow Management