This is the ninth article in a series entitled Stairway to Integration Services. Previous articles in the series include:

• What is SSIS? Level 1 of the Stairway to Integration Services
• The SSIS Data Pump - Level 2 of the Stairway to Integration Services
• Adding Rows in Incremental Loads – Level 3 of the Stairway to Integration Services
• Updating Rows in Incremental Loads – Level 4 of the Stairway to Integration Services
• Deleting Rows in Incremental Loads – Level 5 of the Stairway to Integration Services
• Basic SSIS Workflow Management – Level 6 of the Stairway to Integration Services
• Intermediate SSIS Workflow Management – Level 7 of the Stairway to Integration Services
• Advanced SSIS Workflow Management – Level 8 of the Stairway to Integration Services

Introduction

In the previous three installments, we built a new SQL Server Integration Services (SSIS) package, took a first look at scripting and precedence constraints in SSIS, and examined the MaxConcurrentExecutables package property. We examined, demonstrated, and tested the “On Success”, “On Completion”, and “On Failure” functionality of precedence constraints. We took a first look at SSIS Variables and the SSIS Expression Language, and then applied both to precedence constraints.

In this article, we introduce fault tolerance by examining methods of task execution state management using the MaximumErrorCount and ForceExecutionResult properties. We also study the relationship between SSIS Control Flow task errors, event handlers, and containers.

About SSIS Task Errors

Open the Precedence.dtsx package. Your Control Flow should appear as shown in Figure 1:

Figure 1

Let’s focus on Sequence Container 1. Right-click Script Task 4 and click Enable. Before we execute a test, let’s review the configuration of the precedence constraints inside Sequence Container 1 connecting Script Tasks 2 and 4 to Script Task 4. You may recall the precedence constraint between Script Task 4 and Script Task 3 is configured as shown in Figure 2:

Figure 2

Evaluating the Expression and Constraint means both the expression and the execution status (Value) of the preceding task (Script Task 4) must evaluate to True. In this case, the expression – the value of the SSIS variable MyBool (Boolean) – must be True and the preceding task must fail when executed.

The precedence constraint between Script Tasks 2 and 3 is configured as shown in Figure 3:

Figure 3

Evaluating the Expression only means merely the expression must evaluate to True. In this case, the expression – the value of the SSIS variable MyBool (Boolean) – must be True. The precedence constraint waits for the completion of the preceding task and the execution status of the preceding task is ignored.

Execute a Test

Press F5 to execute the package inside the BIDS debugger. When prompted to Succeed Script Task 4 – as shown in Figure 4 – click the No button:

Figure 4

If you click the No button, Script Task 4 fails. Then Script Task 3 displays a message box informing you it has completed. Acknowledge this message box by clicking the OK button. Your Control Flow should appear as shown in
Figure 5:

Figure 5

Script Task 4 failed because we clicked the No button when prompted. Why did Sequence Container 1 fail?

Events “Bubble”

An error is an event and events bubble. What is bubbling? Click on the Package Explorer tab in BIDS. If you expand the Package\Executables\Sequence Container 1\Executables\ node in the treeview, the Package Explorer should appear as shown in Figure 6:

Figure 6

Script Task 4 failed and raised an error event. That’s why the task turned red in the Control Flow. But then the error event was sent “up the line” to Sequence Container 1. Since an error occurred inside Sequence Container 1, it also failed (and turned red). This transmission of the error event “up in scope” is sometimes called “bubbling.” The error event doesn’t stop at Sequence Container 1; it bubbles again up to the Precedence package, which also fails.

One way to visualize bubbling from the Package Explorer treeview is to imagine the error event “climbing the tree (view).” The default behavior of the error event is to cause the task or container to fail and turn red in the Control Flow. Let’s take a look at manipulating the default behavior.

The MaximumErrorCount Property

All tasks, including Script Task 4, have a MaximumErrorCount property. The default value for the MaximumErrorCount property is 1, which means a single error will cause the task to fail. I can change the value of this property to 99 as shown in Figure 7:

Figure 7

When I execute the package in the BIDS debugger, nothing changes. Script Task 4 still turns red as shown in Figure 8:

Figure 8

Why? The MaximumErrorCount property is really designed for use with containers. As far as I can tell, setting the MaximumErrorCount property on a task has no effect. What happens when I set the MaximumErrorCount property to 99 on Sequence Container 1? Take a look at figure 9:

Figure 9

Sequence Container 1 succeeds.

I most often find I need to fail the package on an error. Occasionally, I ignore errors altogether. Why? The short answer is: ignoring errors does not block event handlers. In Step 10 of this series (Advanced Event Behavior – Step 10 of the Stairway to Integration Services), I explain this behavior. My friend Julie Smith – one of the DataChix – shared this tidbit: Setting the MaximumErrorCount property to 0, as shown
Figure 10, effectively ignores container errors:

Figure 10

The ForceExecutionResult Property

Another way to have Sequence Container 1 succeed is to set the ForceExecutionResult property of the container to something other than the default, which is “None.” Before proceeding, set the MaximumErrorCount property to its default value, 1. Change the ForceExecutionResult property for Sequence Container 1 to Success as shown in Figure 11:

Figure 11

Execute the package in the BIDS debugger. When prompted, allow Script Task 2 to succeed, do not allow Script Task 4 to succeed, and acknowledge Script Task 3 has completed. Your Control Flow should
appear as shown in Figure 12:

Figure 12

Sequence Container 1 succeeds, even though the MaximumErrorCount property is set to fail on 1 error. The ForceExecutionResult property overrides the MaximumErrorCount property.

Error Events

Each time a Control Flow task fails, an Error Event is raised. The error event properties are populated when it is raised, and these properties remain static as the event message is transmitted “up the tree.” Let’s view some of the Error Event properties by adding a Script Task to the OnError Event Handler for Script Task 4.

First, click on Script Task 4 in the Control Flow. Next, click the Event Handlers tab shown in Figure 13:

Figure 13

The Event Handler tab opens and by default displays a non-configured OnError Event Handler for the task selected in the Control Flow, shown in Figure 14:

Figure 14

Note that you can navigate to other executables in the SSIS Package by clicking the Executable dropdown, as shown in Figure 15:

Figure 15

You can also select an event for which you wish to create an Event Handler using the Event Handler dropdown, as shown in Figure 16:

Figure 16

Select Script Task 4 from the Executables dropdown and the OnError event from the Event Handler dropdown. To configure the OnError Event Handler for Script Task 4, click the link labeled “Click here to create an ‘OnError’ event handler for executable ‘Script Task 4’ as shown in Figure 17:

Figure 17

Clicking the link creates an OnError event handler for the Script Task. Take a look at the toolbox for the OnError event handler, as shown in Figure 18:

Figure 18

Look familiar? It should – it’s the Control Flow toolbox! This means the event handlers provide SSIS workflows to respond to events. Event handlers include a set of variables scoped to the event being handled; they are listed in Figure 19:

Figure 19

Note these are System variables and do not display unless you click the Show System Variables button shown in Figure 20:

Figure 20

To demonstrate how these variables work within an event handler, drag a Script Task onto the OnError event handler surface. Open the Script Task editor and change the ScriptLanguage property to “Microsoft Visual Basic 2008”. Click on the ReadOnlyVariables property and then click the ellipsis in the value textbox. When the Select Variables window displays, select the variables System::ErrorCode, System::ErrorDescription, and System::SourceName as shown in Figure 21:

Figure 21

Click the OK button to close the Select Variables window. The Script page on the Script Component Editor should appear as shown in Figure 22:

Figure 22

Click the Edit Script button to open the “ssisscript – Integration Services Script Task” editor. Replace the code in Public Sub Main() with the following VB code:

      Public Sub Main()
        Dim iErrorCode As Integer = _
            Convert.ToInt32(Dts.Variables("ErrorCode").Value)
        Dim sErrorDescription As String = _
            Dts.Variables("ErrorDescription").Value.ToString
        Dim sSourceName As String = _
            Dts.Variables("SourceName").Value.ToString
        Dim sSubComponent As String = _
            "Script Task 4 OnError Event Handler"
        Dim sMsg As String = "Source: " & sSourceName & vbCrLf & _
                             "Error Code: " & iErrorCode.ToString & _
                             vbCrLf & _
                             "Error Description: " & _
                             sErrorDescription
        MsgBox(sMsg, , sSubComponent)
        Dts.TaskResult = ScriptResults.Success
      End Sub

Listing 1

The VB code in Listing 1 begins by creating three VB Script variables – iErrorCode, sErrorDescription, and sSourceName – mapping each to similarly-named SSIS variables scoped to the OnError event handler. The mapping occurs in two steps. The first step is the ReadOnlyVariables property we configured in Figure 21. This exposes SSIS variables to the Script Task. The second step is the Variables object in the Dts namespace, which allows us to access the collection of variables listed in the ReadOnlyVariables property of the Script Task.

The SSIS variables listed in the Script Task’s ReadOnlyVariables property are a collection of variables available for use inside the Script Task. We access these available SSIS variables using the Dts.Variables object via the following syntax:

Dts.Variables(“<Variable Name>”).Value

The Variable Name is case-sensitive.

The Value property of variable accessed from Dts.Variables is an Object. One implication is that we have to cast the object to other data types like String and Integer. The Value property includes a “.ToString” method that will attempt to cast the object to a String data type. VB includes legacy support for casting and converting types in the form of functions like CStr for converting to string and CInt for converting to integer. I opt to use the Convert.To<Data Type> functions in VB for converting to data types other than String.

Script Task Errors

If the Script Task cannot locate the SSIS variable specified in the Script Task’s ReadOnlyVariables property, it will throw an error similar to:

Error: Failed to lock variable "System::ErrorCod" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.".

Listing 2

I generated this error by deleting the final “e” from the System::ErrorCode variable name listed in the Script Task’s ReadOnlyVariables property. I then executed the Script Task by right-clicking the Script Task and clicking Execute Task as shown in Figure 23:

Figure 23

Task execution failed and the error message appears on the Progress (Execution Results) tab as shown in Figure 24:

Figure 24

I can hear you thinking, “Why does the error read: ‘Failed to lock variable,’ Andy?” That is an excellent question. Before the Script Task begins using SSIS variables, it “locks” them. There are good reasons for this behavior that are beyond the scope of this article. After the Script Task successfully locks the SSIS variable(s), the Script Task can then access the SSIS variable(s).

Another Script Task error can occur if you try to access an SSIS variable name inside the Script Task that doesn’t exist in the ReadOnlyVariables or ReadWriteVariables Script Task properties, or if you misspell a SSIS variable name, or even if you do not match the case-sensitivity of the SSIS variable name. You can reproduce this error by first correcting the name of the System::ErrorCode variable listed in the Script Task’s ReadOnlyVariables property, and then opening the Script Task’s script editor. Changing the declaration of the iErrorCode variable to read as shown in Listing 3 will cause this error condition:

Dim iErrorCode As Integer = _
            Convert.ToInt32(Dts.Variables("errorCode").Value)

Listing 3

Note the only change is to the capitalization of the SSIS variable name: “errorCode” instead of “ErrorCode.” The error generated on the Progress / Execution Results tab is long but begins with:

Error: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> Microsoft.SqlServer.Dts.Runtime.DtsRuntimeException: The element cannot be found in a collection. This error happens when you try to retrieve an element from a collection on a container during execution of the package and the element is not there.

On the Progress / Execution Results tab, the error appears as shown in Figure 25:

Figure 25

These errors are tedious to troubleshoot.

If you have been playing along at home, correct the spelling of “ErrorCode” inside the Script Task’s script. A quick unit test – executing the Script Task in the OnError event handler – should yield results similar to those shown in Figure 26:

Figure 26

We are now ready to test Errors in the Precedence.dtsx SSIS package.

Observing an Error

Execute the Precedence SSIS package in the BIDS debugger by clicking the “Play” button or pressing the F5 button. When prompted to succeed Script Task 4 as shown in Figure 27, click the No button:

Figure 27

Clicking No causes Script Task 4 to fail which raises an Error event. The Error event is “heard” by the OnError event handler “listener,” which causes the Script Task on the OnError event handler to execute and display information about the error, as shown in Figure 28:

Figure 28

More about Bubbling

Earlier, we mentioned that events “bubble.” In this case, the Error event generated at Script Task 4 will be transmitted “up the tree” in the scope of the SSIS package to Sequence Container 1. If we configure an OnError event handler for Sequence Container 1, we can observe this occurring.

Stop the BIDS debugger if it is still running. On the Control Flow, select Sequence Container 1. Click the Event Handler tab. As before, click the link to create an OnError event handler for Sequence Container 1. Copy the Script Task from the Script Task 4 OnError event handler and paste it onto the Sequence Container 1 OnError event handler as shown in Figure 29:

Figure 29

Open the Script Task editor and click the Edit Script button. Change one line in Public Sub Main():

Dim sSubComponent As String = _
            "Sequence Container 1 OnError Event Handler"

Right-click the Script Task and click Execute Task. Your results should appear similar to those shown in Figure 30:

Figure 30

Execute the entire SSIS package in the BIDS debugger. When prompted to Succeed Script Task 4, click the No button to generate an Error event from Script Task 4.

As before, the OnError event handler – performing its function as an event listener – “hears” and
responds to the Script Task 4 error event, as shown in Figure 31:

Figure 31

You can tell it’s the Script Task 4 OnError event handler responding because the title bar of the message box contains the text “Script Task 4 OnError Event Handler.” Also, the Script Task that is executing is yellow, and it is on the surface of the Script Task 4 (Executable) OnError event handler.

Click the OK button to acknowledge the message box. The Error event then bubbles to the Sequence Container 1 OnError event handler, as shown in Figure 32:

Figure 32

Again, you can tell it is the Sequence Container 1 event handler firing from the title bar of the message box, the yellow color of the Script Task, and the value selected in the Executable dropdown.

Note the contents of the message box. The Source of the Error event is Script Task 4, and the Error Description and Error Code values do not change. This is very interesting behavior for events in SSIS. The event properties are populated when an SSIS task initially raises the event. Once the event is placed on the messaging bus, these values do not change.

The event will continue to bubble from Sequence Container 1 to the Precedence.dtsx package. As it does, the variable values will remain static, informing any configured listeners that this error initiated at Script Task 4, has an Error Code value of 8, and has the Description “The script returned a failure result.”

Conclusion

In this article, we took a look at SSIS Control Flow task error behavior including Error events, OnError event handlers, and error bubbling. We demonstrated the relationship between event bubbling and containers, and also introduced fault tolerance with the MaximumErrorCount and ForceExecutionResult properties.