Nineth integration Services: Control Flow Task Error

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

Introduction
In the previous three articles, we created a new SSIS package, learned the Script task and precedence constraints, and checked the MaxConcurrentExecutables properties of the package. We examine, demonstrate, and test the impact of precedence constraints on workflows when they are assigned success, finish, and fail. We learned about SSIS variables and expressions and applied them to precedence constraints.
This article, we introduce fault tolerance by examining methods of task execution state management using the Maximumerrorcount and F Orceexecutionresult Properties. We will also learn about the relationship between SSIS Control Flow task errors, event handlers, and containers.
SSIS Task error
Open the PRECEDENCE.DTSX package. Your control flow panel is shown in 9.1:

Figure 9.1
Let's focus on sequence container 1. Right-click Script Task 4 to select Enable. Before we perform the test, let's look at the precedence constraint configuration that connects Script Task 4, Script Task 2, and Script task 3 in sequence container 1. The precedence constraint between Script Task 4 and Script Task 3 is shown in configuration 9.2:

Figure 9.2
The evaluation operation is an expression and a constraint, which means that the execution state (value) of the expression and the previous task (Script Task 4) must be true. In this example, the expression--ssis variable Mybool (Boolean) must be true and the previous task must fail.
The precedence constraint between Script Task 2 and Script Task 3 is shown in configuration 9.3:

Figure 9.3
The evaluation operation is an expression, which means that the expression must be true. In this example, the expression--ssis variable Mybool (Boolean) must be true. The precedence constraint waits until the task is completed and ignores the execution state of the previous task.
Test Execution
Press F5 to execute the package under the bids caller. When prompted succeed Script Task 4 o'clock (Figure 9.4), click the No button:

Figure 9.4
If you click the No button, Script Task 4 will fail. Then script Task 3 Displays the completion message box and clicks the OK button. Your control flow panel should look like 9.5:

Figure 9.5
Script Task 4 failed because no was selected when prompted. Why Sequence Container 1 failed?
event "bubbling"
An error is an event and event bubbling. What is bubbling? Bids Click Package Explorer, expand packages \ executables \ sequence container 1\ executable file, package Explorer should look like 9.6:

Figure 9.6
Script Task 4 fails and causes an error event. That's why task 4 turns red in the control flow. Then the error event is routed to the Sequence container 1. Because an error occurs within the sequence container, it fails itself (turns red). Transmission of error events (up in scope) is sometimes called bubbling. The error event does not stop in sequence container 1, it continues to bubble to the precedence package, and it also fails.
The one-to-visualize bubbling from the package Explorer in the TreeView is to imagine the error event "Climbing the tree (view)." The default behavior of the error event causes the task or container in the control flow to fail and become red.
Maximumerrorcount Property
All tasks, including Script Task 4, have the Maximumerrorcount property. The default value of the Maximumerrorcount property is 1, meaning that a single error causes the task to fail. I will change the value of this property to 99, 9.7, as follows:

Figure 9.7
When I execute the package under the bids debugger, there is no change. Script Task 4 is still red, 9.8 shows:

Figure 9.8
Why? The Maximumerrorcount property also applies to containers. Setting the Maximumerrorcount property on a task has no effect. What happens if I set the Maximumerrorcount property of sequence container 1 to 99? 9.9 is shown below:

Figure 9.9
Sequence Container 1 succeeded. I often find that I need to fail the package at the wrong time. Sometimes, I completely ignore the error. Why? The answer is: ignoring the error does not block the event handler. In the tenth chapter, I will explain this behavior. The Maximumerrorcount property is set to 0 (figure 9.10), which effectively ignores container errors.

Figure 9.10
Forceexecutionresult Property
Another way to make the Sequence container 1 successful is to set the container's Forceexecutionresult property to a non-default value ("None"). Set the Maximumerrorcount property back to 1 before proceeding. Modify the Forceexecutionresult property of sequence container 1 to "Success", as shown in 9.11:

Figure 9.11
Execute the package under the bids debugger. When prompted, let Script Task 2 succeed, let Script task 4 fail, and accept Script Task 3 to complete. Your control flow should be shown in 9.12:

Figure 9.12
Sequence Container 1 succeeds even if the Maximumerrorcount property is set to 1. The Forceexecutionresult property overrides the Maximumerrorcount property.
Error Events
Each failure of the control Flow task causes an error event. The Error event properties was populated when it was raised, and these properties remain static as the event message is TRA Nsmitted "Up the tree." Create a "OnError" event handler for Script Task 4 by adding one of the scripting tasks.
First, click Script Task 4 in the control flow. Next, click on the Event Handler tab (Figure 9.13):

Figure 9.13
The event handler opens, by default displaying the configured "OnError" event handler for the selected task for the control flow. 9.14 is shown below:

Figure 9.14
Note You can navigate to the other executables in the SSIS package by clicking the executable file drop-down menu, 9.15:

Figure 9.15
You can also select events from the event handler drop-down menu for the event handler you want to create, as shown in 9.16:

Figure 9.16
The executable drop-down menu selects the Script Task 4, and the event handler drop-down menu selects OnError. In order to configure the event handler, click the link tag "Click here to create a" OnError "event handler for executable" Script Task 4 ", 9.17 is shown below:

Figure 9.17
Click the link to create a onerror event handler for script Task 4. Take a look at the toolbox of the OnError event handler, as shown in 9.18:

Figure 9.18
It looks familiar? It should be--it's the Control flow Toolbox! This means that the event handler provides the SSIS workflow to respond to the event. The event handler includes a set of variables for handling events, as shown in 9.19:

Figure 9.19
Note that these are system variables, unless you click the Show System Variables button (figure 9.20) to display.

Figure 9.20
To demonstrate how variables work in an event handler, drag a script task to the event handler. Open the Script Task Editor and modify ScriptLanguage to Microsoft Visual Basic 2008. Click the ReadOnlyVariables property and click the ellipsis of the Style box. Open the Select Variable dialog box and choose Variables System::errorcode,system::errordescription and system::sourcename,9.21 as shown:

Figure 9.21
Click the OK button to close the Select Variable window. Script page 9.22 of the Script Task Editor is as follows:

Figure 9.22
Click the Edit Script button to open the Ssisscript–integration Services Script Task Editor. Replace the code in the public Sub Main () with the following VB code:

       Public SubMain ()DimIerrorcode as Integer=_ Convert.ToInt32 (Dts.variables ("ErrorCode"). Value)DimSerrordescription as String=_ Dts.variables ("errordescription"). Value.tostringDimSsourcename as String=_ Dts.variables ("SourceName"). Value.tostringDimSsubcomponent as String= _            "Script Task 4 OnError Event Handler"        DimSMsg as String="Source:"& Ssourcename & VbCrLf & _                             "Error Code:"& Ierrorcode.tostring &_ vbCrLf& _                             "Error Description:"&_ serrordescriptionMsgBox(SMSG,, ssubcomponent) Dts.taskresult=scriptresults.successEnd Sub

View Code

Code 9.1
The VB code in Code 9.1 creates three VB Script variables-ierrorcode, serrordescription, and ssourcename-mapping each to similarly-named SSIS variables Scoped to the OnError event handler. Mapping takes place in two steps. The first step is the ReadOnlyVariables property of Figure 9.21 configuration. This exposes the SSIS variable to the Script task. The second step is the variable object in the DTS namespace, which allows us to access the set of variables in the ReadOnlyVariables property of the Script task.
The list of SSIS variables in the Script Task ReadOnlyVariables property is a collection of variables available in the Script task. We use the Dts.variables object to access these available SSIS variables:

Dts.variables ("<variable name>"). Value

Code 9.2
The Value property of an access variable from Dts.variables is an object. We are going to convert this object to another data type (such as String and Integer). The Value property contains a ". The ToString method attempts to convert the object to a string data type.
Script Task Error
If the Script task cannot locate the SSIS variable in the ReadOnlyVariables property of the Script task, it throws an error similar to the following:
error: Unable to lock variable "System :: Errorcod "Read access, error 0xc0010001" variable not found. This behavior occurs if you try to retrieve a variable from the container's Variables collection during the execution of the package, but the variable is not there. The variable may have changed the name or was not created. ”。
I generated this error by deleting the last letter "E" of the System::errorcode variable in the ReadOnlyVariables property of the Script task. Right-click the Script task to select the execution task, 9.23:

Figure 9.23
Task execution failed with error message on progress (execution Result) tab, 9.24:

Figure 9.24
Why the error is: cannot lock the variable. This is a great question. When the Script task starts using SSIS variables, you need to lock them. The explanation of this behavior is beyond this article. After the script task successfully locks the SSIS variable, the Script task can access the SSIS variable.
If you try to access an SSIS variable name that does not exist in the ReadOnlyVariables or Readwritevariables Script task property in the Script task, or if you misspelled the SSIS variable name, Or even if you do not match the case of the SSIS variable name, another Script task error is generated. You can generate this error by using the following method. First fix the System::errorcode variable name in the Script task's readonlyvariables, and then open the Script Task Script Editor. The declaration for modifying the Ierrorcode variable is shown in code 9.3:

 dim  ierrorcode as  integer  =  " errorcode  " ). Value) 

Code 9.3
Note the only modification is to modify the name of the SSIS variable to "ErrorCode" to "ErrorCode". The error in the progress (execution result) tab is very long, but starts with the following:
error: System.Reflection.TargetInvocationException: The target of the call has an exception. ---> Microsoft.SqlServer.Dts.Runtime.DtsRuntimeException: The element cannot be found in the collection. This error occurs if an element is attempted to be retrieved from the collection of containers during the execution of the package, but the element is not here.
on the Progress (Execution Results) tab, error message 9.25:

Figure 9.25
These errors are difficult to resolve. The
corrects the spelling "ErrorCode" in the script in the Script task. In the event handler execution Script task, the result is similar to Figure 9.26:

Figure 9.26
observe error
Execute precedence package under Bids debugger. When prompted succeed Script Task 4 (Figure 9.27), click the No button:

Figure 9.27
Click No causes Script Task 4 to fail, resulting in an error event. This error event is monitored by the OnError event handler, causing the script task on the OnError event handler to execute and display an error message, 9.28:

Figure 9.28
More about bubble
Front, We mentioned event bubbling. In the example, the error event generated by Script Task 4 is transmitted (up the tree) to sequence container 1. If we configure a onerror event handler for Sequence container 1, we can observe this happening.
Stop the bids debugger, in the control flow select Sequence Container 1, click the Event handler tab. As before, click the "Click here for executable" sequence container 1 "to create a" OnError "event handler link. In the Script Task 4 event handler, copy the scripting tasks, and then paste it into the event handler for Sequence container 1, 9.29:

Figure 9.29
Open the Script Task Editor and click the Edit Script button. Modify the ssubcomponent behavior in the public Sub Main ():

 dim  ssubcomponent as  string  =  " sequence Container 1 OnError Event Handler   

Code 9.4
Right-click the Script task and select Perform task. Your results are similar to Figure 9.30:

Figure 9.30
Executes the entire SSIS package under the bids debugger. When prompted succeed Script Task 4 o'clock, click the No button to generate an error event.
As before, the OnError event handler listens and responds to the script Task 4 Error event, as shown in 9.31:

Figure 9.31
You can say that it is the response of the Script task 4OnError event handler because the caption of the message dialog box contains the Script Task 4 OnError event Handler text. The same script task being executed is yellow, and it is in the OnError event handler of Script Task 4.
Click OK, and the error event bubbles to the OnError event handler for Sequence container 1, as shown in 9.32:

Figure 9.32
Depending on the title of the message dialog box, you can tell that this is the event handler for Sequence container 1.
Note the contents of the message dialog box. The source of the error event is script Task 4, and the error description and error code values are not changed. This is a very interesting behavior in SSIS events. Event properties are populated when an SSIS task initially generates an event. Once events are placed on the message bus, their values are not changed.
The event continues to bubble from sequence container 1 to the PRECEDENCE.DTSX package. The value of the variable remains static, and any configured listener displays the source: script Task 4, error code: 8, error description: The scripts returned the failed result.
Summarize
In this article, we learned about SSIS Control Flow task error behavior, including error events, onerror event handlers, and error bubbling. We demonstrated the relationship between event bubbling and the container, as well as the maximumerrorcount and Forceexecutionresult fault tolerance properties.

Nineth integration Services: Control Flow Task Error