This is the tenth 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
• Control Flow Task Errors – Level 9 of the Stairway to Integration Services

Introduction

In the previous installment, we introduced fault tolerance by examining methods of task execution state management using the MaximumErrorCount and ForceExecutionResult properties. We also studied the relationship between SSIS Control Flow task errors, containers, and event handlers.

This installment focuses on the behavior of events. In this article, we share two methods for manipulating the default behavior of event transmission (bubbling). We also introduce the Parent-Child pattern and demonstrate event behavior in the Parent-Child pattern.

About SSIS Task Events

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

Figure 1

As in Step 9, we focus on Sequence Container 1.

In Step 9 of this series, we created OnError event handlers – which we described as event “listeners” – on Script Task 4 and Sequence Container 1. To each OnError event handler, we added a Script Task that displays a message box containing the values of the following SSIS OnError event handler variables:

• System::ErrorCode
• System::ErrorDescription
• System::SourceName

Before we begin testing, let’s verify and/or change some settings from the previous build of the Precedence.dtsx SSIS package. First, click on Sequence Container 1 and then press the F4 key to display Properties. Make certain the ForceExecutionResult property remains set to “Success”. Next click on Script Task 4 and press the F4 key to display Properties. Change the MaximumErrorCount property to 1.

Before we make any more changes, execute the Precedence.dtsx SSIS package in the BIDS (Business Intelligence Development Studio) debugger by pressing the F5 key. Your response to the prompt “Succeed Script Task 2?” does not matter for the purposes of demonstration, but now and throughout this article we will opt to click the Yes button shown in Figure 2 to cause the Script Task to succeed:

Figure 2

When prompted to “Succeed Script Task 4?” – as shown in Figure 3 – click the No button to raise an Error event from Script Task 4:

Figure 3

When Script Task 4 raises an Error event, the OnError event handler we configured in Step 9 “hears” the Error event and executes, displaying a message box similar to that shown in Figure 4:

Figure 4

But the Error event doesn’t stop there, it continues to bubble up in scope – traversing the execution stack to the next level: Sequence Container 1, which contains Script Task 4. We see a similar message box displayed from the OnError event handler configured on Sequence Container 1 shown in Figure 5:

Figure 5

Once we acknowledge this message box, another message box displays informing me that Script Task 3 has completed. Acknowledging the Script Task 3 message box allows the Precedence.dtsx SSIS package to
complete execution in the BIDS debugger, and it appears as shown in Figure 6:

Figure 6

Note that Sequence Container 1 succeeds. Or, at least, reports success. Why?

Events and Execution Status

In Step 9 we set the ForceExecutionResult property of Sequence Container 1 to “Success” and we never changed it back to the default setting (“None”). This was intentional (and we verified this earlier) to demonstrate the interaction between the ForceExecutionResult property of Sequence Container 1 and Sequence Container 1’s response to Error events. While the ForceExecutionResult property overrides the execution status of Sequence Container 1, it does not interfere with Sequence Container 1’s ability to “hear” and respond to Error events. It may appear that Sequence Container 1 ignores Error events when ForceExecutionResult is set to “Success” but that is inaccurate and we just proved it. Please note that there is a way to ignore event handlers, and we will demonstrate this functionality at the end of this article. The interaction between event handlers and the ForceExecutionResult property provides flexibility to data integration developers, facilitating fault tolerance. This is important: An error can occur and be “heard” (and responded to) without failing the Control Flow.

Kicking it Down a Notch

It is important to remember that event bubbling will cause the Error event to continue transmission up the execution stack. In our example, the next level of scope is the Precedence.dtsx SSIS package Control Flow which is representative of the SSIS package itself.

Since we have not changed the MaximumErrorCount or ForceExecutionResult properties for the Precedence.dtsx SSIS package, the package will fail when the Error bubbles to the package container.

This may or may not be desired behavior.

We are faced with a design decision. We can either: A) add fault tolerance at each level of scope above the task for which we want to trap an error – and thereby add fault tolerance to the entire package (whether we desire this or not); or B) we can interrupt the default bubbling behavior of the Error event at Script Task 4. Let’s go with option B.

Return to Script Task 4’s OnError event handler and display the Variables window by clicking the SSIS
dropdown menu, and then click Variables as shown in Figure 7:

Figure 7

Once the Variables window displays, select the Show System Variables button shown in Figure 8:

Figure 8

The System::Propagate variable is a Boolean variable that defaults True. Shown in Figure 9, System::Propagate is the event bubbling control variable:

Figure 9

Click on the Value column and change the default value from True to False as shown in Figure 10:

Figure 10

Execute the Precedence.dtsx SSIS package in the BIDS debugger. When prompted to “Succeed Script Task 2,” click Yes; when prompted to “Succeed Script Task 4,” click No. Script Task 4’s OnError event handler executes as shown in Figure 11:

Figure 11

The screenshot shown in Figure 12 demonstrates Script Task 3 is executing and Sequence Container 1’s OnError event handler has not executed:

Figure 12

The Error event raised by Script Task 4 was “heard” by Script Task 4’s OnError event handler, but it did not bubble to Sequence Container 1’s OnError event handler.

Programmatic Control

We can programmatically control event bubbling inside a Script Task. To demonstrate, open the Script Task editor on Script Task 4’s OnError event handler, and then add the System::Propagate SSIS variable to the ReadWriteVariables property as shown in Figure 13:

Figure 13

Open the .Net script editor by clicking the Edit Script button. Edit the code in Public Sub Main(),
adding the If/Then statement shown in Listing 1 starting at Line 56, as shown in Figure 14:

Figure 14

        If iErrorCode = 8 Then
            Dts.Variables("Propagate").Value = False
        Else
            Dts.Variables("Propagate").Value = True
        End If
Listing 1

This change in the .Net script adds conditional logic that controls the value of the System::Propagate variable, depending on the value of the System::ErrorCode variable (via the VB.Net Script variable iErrorCode, which reads System::ErrorCode). Before running a test execution of the Precedence.dtsx SSIS package, change the default value of the System::Propagate SSIS variable Script Task 4’s OnError
event handler back to True as shown in Figure15:

Figure 15

Setting System::Propagate to True turns event bubbling on by default. If the OnError event doesn’t bubble now, it is because we are changing the System::Propagate SSIS variable value from True to False in the Script. Execute the Precedence.dtsx SSIS package in the BIDS debugger by pressing F5. When prompted to “Succeed Script Task 2,” click Yes; when prompted to “Succeed Script Task 4,” click No.

Observing Script Task 4’s OnError event handler, we see the Script Task executes and displays error event details, including an ErrorCode value 8, shown in Figure 16:

Figure 16

Figure 17 demonstrates we successfully changed the System::Propagate variable value from True – the default value we changed before executing this test – to False inside the Script code, as Script Task 3 executes and Sequence Container 1’s OnError event handler does not execute:

Figure 17

Event bubbling has been programmatically managed and we have achieved dynamic fault tolerance in SSIS.

We can test this by altering the ErrorCode value raised by Script Task 4. On the Control Flow, open the Script Task 4 editor, and then open the script editor by clicking the Edit Script button. As I edit the code in the If/Then code block that responds to the message box button click, shown in Figure 18, you can see the IntelliSense for the Dts.Events.FireError method (if you look closely, it is a shadow to my screenshot capture software):

Figure 18

Comment out the line:

Dts.TaskResult = ScriptResults.Failure

Just beneath the line you just commented out, add the line:

            Dts.Events.FireError(-1001, "Script Task 4", _
                                 "Script Task 4 failed!", "", 0)

Your code should appear as shown in Figure 19 and Listing 2:

Figure 19

      Public Sub Main()
        Dim sTaskName As String = Dts.Variables("TaskName").Value.ToString
        Dim iResponse As Integer
        iResponse = MsgBox("Succeed " & sTaskName & "?", _
                           MsgBoxStyle.YesNo + MsgBoxStyle.Question, _
                           sTaskName & " Success Question")
        If iResponse = vbYes Then
            Dts.TaskResult = ScriptResults.Success
        Else
            'Dts.TaskResult = ScriptResults.Failure
            Dts.Events.FireError(-1001, "Script Task 4", _
                                 "Script Task 4 failed!", "", 0)
        End If
    End Sub

Listing 2

This code alters the response to the user clicking the No button when prompted to “Succeed Script Task 4?” Script Task 4 now generates a custom error code and error description; -1001 and “Script Task 4 failed!” respectively. The argument list for the Dts.Events.FireError method are: ErrorCode, SubComponent, Description, HelpFile, and HelpContext, respectively.

To test, execute the Precedence.dtsx SSIS package in the BIDS debugger by pressing F5. When prompted to “Succeed Script Task 2,” click Yes; when prompted to “Succeed Script Task 4,” click No. Observing Script Task 4’s OnError event handler, we see the message box displays properties of an Error event that are different from before, as shown in Figure 20:

Figure 20

Because the code in the Scrip Task contained in Script Task 4’s OnError event handler only sets System::Propagate to False when the System::ErrorCode variable value equals 8, this event bubbles to Sequence Container 1’s OnError event handler, as shown in Figure 21:

Figure 21

In this demonstration, we used an If/Then conditional statement in Script Task 4’s OnError script to isolate a single ErrorCode value. But you can use other conditional statements, such as Select Case in VB or Switch in C#. You have additional options because event handlers expose the Control Flow toolbox.

Bubble++

Event bubbling can extend beyond the domain of a single SSIS package. To examine this behavior of events, we have to take a minute and introduce the Parent-Child SSIS design pattern.

The Parent-Child SSIS Design Pattern

The SSIS Execute Package Task is used to call an SSIS package from another SSIS package. When one package calls another, the calling package – the one containing the Execute Package Task – is referred to as the parent package while the package called by the Execute Package Task is referred to as the child package. It turns out this description is more than merely semantics; it provides a good description of some interesting behavior and interaction between the packages.

Let’s add a new SSIS package to the My_First_SSIS_Project solution to demonstrate. In Solution Explorer, right-click the SSIS Packages virtual folder, and then click New SSIS Package as shown in Figure 22:

Figure 22

A new package is created and added to the solution. Because this solution already contains an SSIS package named Package.dtsx, the new package is named Package1.dtsx. Right-click Package1.dtsx and then click Rename to change the name of the new SSIS package to Parent.dtsx as shown in Figure 23:

Figure 23

When you press the Enter key, you are prompted and asked if you want to rename the Package object as well, as shown in Figure 24:

Figure 24

Always answer this prompt by clicking the Yes button. The SSIS packages in Solution Explorer represent two objects: a file in the file system and a Package object in the solution. When you rename a package, the file name is changed first and then you are asked if you want to also rename the package object. I cannot think of a good reason you would want the package file named one thing and the package object named something different. There may be a good use case for this, I am simply unaware of one.

You now have a package named Parent.dtsx in your SSIS solution as shown in Figure 25:

Figure 25

Drag an Execute Package Task onto the Control Flow as shown in Figure 26:

Figure 26

Double-click the Execute Package Task to open the Execute Package Task Editor. Since we are going to start an SSIS package stored in the file system, change the Location property to “File system” as shown in Figure 27:

Figure 27

We now need to configure an SSIS File Connection Manager with the location of the Precedence.dtsx package in the file system. Click the dropdown on the Connection property and click the “<New
connection…>” item as shown in 28:

Figure 28

When the File Connection Manager Editor opens, click the Browse button. When the Select File window displays, navigate to the location of the Precedence.dtsx file in your file system. If you navigate to the folder containing your SSIS solution (…My_First_SSIS_Project), you will find the Precedence.dtsx file located at …My_First_SSIS_Project\My_First_SSIS_Project\Precedence.dtsx as shown in Figure 29:

Figure 29

Click the Precedence.dtsx file and then click the Open button. This returns you to the File Connection Manager Editor and places the full path to the Precedence.dtsx file in the File textbox as shown in Figure
30:

Figure 30

Click the OK button to complete creation of the File Connection Manager and return to the Execute Package Task Editor, as shown in Figure 31:

Figure 31

Click the OK button to close the Execute Package Task Editor. When we execute the Parent.dtsx SSIS package in the BIDS debugger, it will call the Precedence.dtsx SSIS package. Before we execute the SSIS package in the BIDS debugger, I would like to show you a handy trick for testing SSIS packages participating in the Parent-Child SSIS design pattern. If both Parent.dtsx and Precedence.dtsx are not open in BIDS, open both of them now. They should appear similar to Figure 32:

Figure 32

Left-click and hold on the tab labeled “Precedence.dtsx” and drag it to the right a few pixels, and then release the left-mouse button. When you release the left mouse button, you are prompted to create a new
Tab Group – Horizontal or Vertical. Select New Vertical Tab Group as shown in Figure 33:

Figure 33

BIDS will arrange the packages as shown in Figure 34, which makes following Parent-Child executions much easier:

Figure 34

Click in the white space of the Parent.dtsx SSIS package’s Control Flow to make sure Parent.dtsx is selected. Press the F5 key to execute the Parent.dtsx SSIS package – which will call Precedence.dtsx – and when prompted to “Succeed Script Task 2,” click Yes; when prompted to “Succeed Script Task 4,” click No. Acknowledge the OnError event handler messages generated by Script Tasks on Script Task 4’s OnError event handler and Sequence Container 1’s OnError event handler. Acknowledge the message box informing you Script Task 3 has completed. Execution should complete and BIDS should appear as shown in Figure 35:

Figure 35

In Precedence.dtsx, note Script Task 4 failed and Sequence Container 1 succeeded. In Parent.dtsx, note the Execute Package Task failed. This condition exists because the ForceExecutionResult property of Sequence Container 1 remains set to “Success.” An Error event bubbled through Sequence Container 1, though. We saw it just now; it appeared as shown in Figure 36:

Figure 36

Something else happened that we cannot observe without more development effort – the Precedence.dtsx SSIS package failed. Let’s add an OnError event handler to the Precedence.dtsx package to prove this. Copy the Script Task from Sequence Container 1’s OnError event handler. Navigate to the Precedence package’s OnError event handler, click the link to create it, and paste the Script Task there
as shown in Figure 37:

Figure 37

Open the Script Task Editor, and then click the Edit Script button to open the script editor. Edit Line 46 of the code shown in Figure 38 to read as shown in Listing 4:

Dim sSubComponent As String = _
            "Precedence Package OnError Event Handler"

Listing 4

Figure 38

Click in the white space of Precedence.dtsx’s Control Flow to make sure it is selected, and then press the F5 key to execute the Precedence.dtsx SSIS package in the BIDS debugger. When prompted to “Succeed Script Task 2,” click Yes; when prompted to “Succeed Script Task 4,” click No. Observe the Precedence OnError event handler as the OnError event handlers execute and display message boxes. You will now see a message box displayed when the Precedence OnError event handler executes, as shown in Figure 39:

Figure 39

Copy the Script Task in the Precedence OnError event handler to the clipboard. Navigate to the Parent package’s OnError event handler, create the event handler, and paste the Script Task, as shown in Figure
40:

Figure 40

Open the Script Task Editor, click the Edit Script button to open the script editor, and replace the line in Listing 5:

Dim sSubComponent As String = _
            "Precedence Package OnError Event Handler"

Listing 5

with the line in Listing 6:

Dim sSubComponent As String = _
            "Parent Package OnError Event Handler"

Listing 6

as shown in Figure 41 on Lines 46-47:

Figure 41

Click in the white space of the Parent.dtsx SSIS package’s Control Flow to make sure Parent.dtsx is selected. Press the F5 key to execute the Parent.dtsx SSIS package – which will call Precedence.dtsx – and when prompted to “Succeed Script Task 2,” click Yes; when prompted to “Succeed Script Task 4,” click No. Acknowledge the OnError event handler messages generated by Script Tasks on Script Task 4’s OnError event handler, Sequence Container 1’s OnError event handler, and the Precedence package’s OnError event handler. Note the Parent package’s OnError event handler executes next, as shown in Figure 42:

Figure 42

There are a couple interesting things I wish to point out here.

First, in the Parent-Child SSIS design pattern, the Error event bubbles from the “bottom” of the Precedence.dtsx SSIS package – Script Task 4 – to the “top” of the Parent.dtsx SSIS package. Figure 44 shows my “artist’s concept” of what is really and truly happening here. The Precedence.dtsx package acts as if it is “in scope” of the Parent.dtsx package’s Execute Package Task – like it is in an “Executables” virtual folder of the Execute Package Task. In real life, you will never view a Package Explorer diagram that displays the relationship I created in Figure 43. Execute Package Tasks do not have an Executables virtual folder, and if they did you would not see the Package Explorer from the child package included therein (at least not in a current version of SSIS at the time of this writing). But this represents the behavior of event bubbling in the Parent-Child SSIS design pattern:

Figure 43

Second, the Error event, originally generated at Script Task 4, is still configured with its original variable values. The ErrorCode, ErrorDescription, and SourceName SSIS variable values remain static as the Error event bubbles – even when it bubbles from the child package to the parent package, as shown in Figure
44:

Figure 44

This behavior applies to all SSIS Task events, not just the OnError events. If one package is executed from another using the Execute Package Task, events bubble to the top of the parent package by default. And from our previous demonstrations, you know how to change the default behavior if you want to manage the bubbling behavior of certain event codes.

One Last Thing

I can hear you thinking, “Well then, Andy; do we need to manage events in a Parent-Child SSIS design pattern at the child level? Can’t we just manage them all at the parent level?” The answer is, “It depends.” If that behavior fits your enterprise event management design, then yes. But there are valid use cases for trapping individual SSIS task and container events at the child package scope, even if you are allowing most events to bubble to the top of the parent package. The key, I believe, is now you know more about the flexibility you have in configuring these events. You now have options, and options are good.

The last thing I want to discuss in this article is the DisableEventHandlers property. Executables in SSIS – SSIS tasks and containers, in other words – each have a DisableEventHandlers property. The DisableEventHandlers property is inherited by executables within the scope of the task or container. This is an important distinction because it is not true for all properties. MaximumErrorCount and ForceExecutionResult properties are not inherited, for example. To demonstrate, click the Precedence.dtsx Control Flow and then press the F4 key to display package properties. Change the DisableEventHandlers property to True as shown in Figure 45:

Figure 45

Click in the white space of the Parent.dtsx SSIS package’s Control Flow to make sure Parent.dtsx is selected. Press the F5 key to execute the Parent.dtsx SSIS package – which will call Precedence.dtsx – and when prompted to “Succeed Script Task 2,” click Yes; when prompted to “Succeed Script Task 4,” click No. The next message box displayed is from the Parent OnError event handler. Why? Because the Precedence.dtsx SSIS package’s event handlers are now disabled.

Again, you may wish to use child package event handlers to isolate certain events and halt bubbling. There are valid use cases for such designs.

Conclusion

In this article, we focused on the behavior of events. We demonstrated two methods for altering the default behavior of event bubbling; disabling event handlers using the DisableEventHandlers property and manipulating the System::Propagate SSIS variable value inside an event handler using a Script Task. We introduced the Parent-Child pattern and examined event behavior within the Parent-Child pattern.