This is the 17th day of my participation in the November Gwen Challenge. Check out the event details: The last Gwen Challenge 2021

Translation reference

Advanced Event Behavior — Level 10 of The Stairway to Integration Advanced Event Behavior — Level 10 of The Stairway to Integration Services “Services” aims to have a comprehensive and clear understanding of SSIS. All contents are carried out in practice on the basis of the original text. Due to various reasons such as version differences and personal questions, the principle of complete translation is not adopted.

Thanks for your support!

In this section we will focus on the behavior of events. We share two approaches to managing the default behavior of event transport (bubbling — bubbing) and introduce the parent-child pattern and the event behavior within it

SSIS Task Events

Open the “Precedence. DTSX “package and the current control flow should look like the following:

In the previous section, we created OnError event handlers — called “listeners” — on “Script Task 4” and “sequence container 1”. In each OnError event handler we add a script task to display a message box containing the following SSIS variable values:

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

Before testing, let’s verify some of the Settings changes made in the previous “precedence. DTSX” package build. First, click “Sequence Container 1”, then press “F4” to display the properties, make sure the ForceExecutionResult property is still set to “Success”, then click “Script Task 4” and press “F4” to display the properties. Change the MaximumErrorCount property to 1.

Events and Execution Status

The ForceExecutionResult property of “sequence container 1” was set to “Success” and was not changed to the default value (“None”). The intent is to demonstrate the correlation between the ForceExecutionResult property of “sequence container 1″ and the response of” sequence container 1″ to an error event. When the ForceExecutionResult attribute overwrites the execution state of the sequence container 1, it does not interfere with the ability of the sequence container 1 to listen and respond to error events.

When ForceExecutionResult is set to “Success”, “sequence container 1” may ignore error events, but this is not certain, we just proved it. Note that there is a special way to ignore event handlers, which we’ll demonstrate at the end of this article.

The interaction between the ForceExecutionResult property and the event handler provides flexibility for data integration developers. This is important: errors can occur and be listened for (and responded to) without allowing the control process to fail.

Prevents events from bubbling

Gap handling (event bubbling “defect “) – Prevents event bubbling

It is important to remember that event bubbling causes Error events to continue up the execution stack. In our example, the next layer of scope is the “precedence.dtsx” packet control flow, which represents the SSIS packet itself.

Because we have not changed the MaximumErrorCount or ForceExecutionResult properties of the “precedence. DTSX” package, the package will fail when the error bubbles up into the package container.

This may or may not be the desired behavior.

We were faced with a design decision:

  • A — Add fault tolerance to the entire SSIS package (whether needed or not) by adding fault tolerance at each scope level above the task we want to catch the error.

  • B — We can interrupt the default bubbling of Error events in “Script Task 4”.

Let’s look at choice B.

Return to the OnError event handler for “Script Task 4”, click on the SSIS drop-down menu at the top and click on “Variables” to display the Variables window. Set the grid option to display “System variables”.

The “System::Propagate” variable is a Boolean variable and defaults to True. System::Propagate” is the control variable for event bubbling.

Click on the Value column to change the default Value True to False.

In the debugger, execute the “Precedence. DTSX “SSIS package and click” No “when the “Script Task 4″ dialog box is displayed. The OnError handler for Script Task 4” executes:

Subsequently, the OnError event handler for “sequence container 1” did not execute, and “Script Task 3″ and” sequence container 1″ completed execution.

The error event raised by “Script Task 4” is heard by the OnError event handler of “Script Task 4”, but does not bubble up to the OnError event handler of “sequence container 1”.

Programmatic Control

We can program events to bubble in scripting tasks.

Open the Script Task editor in the OnError event handler for “Script Task 4” and add the “System::Propagate” variable to the “ReadWriteVariables” property.

Click the “Edit Script” button to open. Net script editor, add the following if statement to the Main function:

Based on the value of the “System::ErrorCode” variable, add conditional logic control for the value of the “System::Propagate” variable.

Change the default of the “System::Propagate” variable in the OnError event handler of “Script Task 4” to the original True before executing the “Precedence. DTSX “SSIS package test.

Run the “precedence. DTSX” package in the debugger by pressing “F5”, and click “No” in the “Script Task 4” prompt. You can observe that the “Script Task “of the OnError event handler for “Script Task 4” executes and displays details of the error event.

Script Task 3 and Sequence container 1 are executed without the error event bubbling up.

Event bubbling has been managed programmatically, and we have implemented Dynamic fault tolerance in SSIS.

Custom error

We can test this by changing the ErrorCode value raised by “Script Task 4”.

On Control Flow, open the Script Task 4 editor, and then click the Edit Script button to open the Script editor. Edit the code in the if block in response to the click of the message box button, as shown below, and you can see the smart prompt for the dTS.events.fireError method.

Dts.TaskResult = (int) scriptresults.failure;

Add:

Dts.Events.FireError(- 1001., sTaskName, Script Task 4 failed to execute!."".0);
Copy the code

The current code modifies the user’s response to the “No” button in the successful “Script Task 4” query, Generate a custom error code and error description — 1001 and Script Task 4 failed! .

The arguments to the dTS.events. FireError method are ErrorCode, SubComponent, Description, HelpFile, and HelpContext.

Run the “precedence. DTSX” package. Click “No” in the message prompt box of “Script Task 4”, the message box displayed in the OnError event handler of “Script Task 4” will be as follows:

Because the code for “Script Task” in the OnError event handler for “Script Task 4” only sets “System::Propagate” to False if the “System::ErrorCode” variable is 6. Therefore, the current event will still bubble up to the event handler of “sequence container 1”.

This demonstration just isolates a single ErrorCode value in an if conditional statement. Other conditional statements can be used as needed, such as Switch in C# or Select Case in VB. You can even use other options in the SSIS toolkit.

The bubbling + +

Event bubbling can extend beyond the scope of a single SSIS package. To examine this behavior of events, we must take a moment to introduce the parent-child SSIS design pattern.

Parent-child SSIS Design Pattern

The SSIS execution package task is used to invoke an SSIS package from another SSIS package.

When a Package calls another Package, the Package that is invoked (the Package that contains the Execute Package Task) is called the parent Package, and the Package that is invoked by the Execute Package Task is called the child Package. As it turns out, this description is not only semantic, but also a good description of some interesting behavior and interactions between packages.

Design father and son bag

Add a new SSIS package to the “FirstSSIS” project solution.

In Solution Explorer, right-click the “SSIS Packages “virtual folder and click” New SSIS Package “.

A new package named “package1.dtsx” is created and added to the solution. Right-click the package and click “Rename” to change the name of the new package to “parent.dtsx”.

Open the “Parent. DTSX “Package and drag an” Execute Package Task “into the control flow.

Double-click “Execute Package Task” to open the Execute package task editor.

The Execute Package Task is used to Execute another SSIS Package. The other SSIS package can be stored as a file or in SQL Server. In the Execute Package Task Editor, you need to configure the operation of the subpackage, specify how to reference the subpackage, which subpackage (the location of the subpackage), etc.

There are two ways to add subpackages to the execution package task editor: Project Reference and External Reference. Project references can easily refer to other packages under the current solution. External references are used to refer to packages stored elsewhere (File system or SQL Server)

Because we want to start another SSIS package under the current solution, so under the “project reference”, “PackageNameFromProjectReference” attribute selection “Precedence. DTSX” package.

Click OK to close the Execute Package Task Editor. When the “parent. DTSX” package is executed in the debugger, it will call the “precedence. DTSX” package.

SSIS layout adjustments are shown together with parent packs

Before I execute the SSIS package in the debugger, I want to show you a handy trick to test the SSIS package that participates in the parent-child SSIS design pattern.

Make sure that Parent. DTSX and Precedence. DTSX are both open. Left-click and grab the “precedence. DTSX” label, then drag it right away from the original label, then drag it to the layout line shown in the image below, release the left mouse button.

Finally, the two packages in parent-child mode are shown as follows:

Performs error event handling between packages and parents

Click on the blank area of the control flow of the “parent. DTSX” package to make “parent. DTSX” selected. Press “F5” to execute the “parent. DTSX” package — it will call “Precedence. DTSX “.

Click “Yes” in the “Script Task 2” prompt box, click “No” in the “Script Task 4” prompt box. Verify the OnError event handler for “Script Task 4” and the OnError event handler message box generated by “Script Task “in the event handler for” sequence container 1″ and verify the completion prompt for “Script Task 3”.

When the execution is complete, it should look like the following:

In “Precedence. DTSX “, “Script Task 4” fails and “sequence container 1” succeeds. Failed to execute package task in Parent. DTSX “. This is the case because the ForceExecutionResult property of “sequence container 1” retains the “Success” setting and an error event bubbles through” sequence container 1″.

Without more development attempts, we would not be able to observe other things happening — such as the “precedence.dtsx” package failing to execute. To demonstrate this, add an OnError event handler to the “Precedence. DTSX “package.

Copy the “script task” of the “Sequence container 1” OnError event handler, navigate to the “Precedence” package OnError event handler, click the” link “to create it, and then paste the” script task “into this.

Open the Script Task Editor and click the Edit Script button. Locate the location of the “sSubComponent” definition. Change to the following code:

var sSubComponent = "Precedence Package OnError Event Handler";
Copy the code

Click “precedence. DTSX” in the blank area of the control flow to make sure it is selected. Press “F5” to execute the “Precedence. DTSX “package. In the “Script Task 4” dialog box, click “No”.

Copy the “script task” in the Precedence OnError event handler, navigate to the “Parent” package OnError event handler, create the event handler, and paste the “script task”.

Also, open the script task editor, open the “Edit Script” button, and modify the initialization of “sSubComponent”.

var sSubComponent = "Parent Package OnError Event Handler";
Copy the code

Click on the blank area of the “parent.dtsx” package control flow to make sure “parent.dtsx” is selected. Press “F5” to execute the “parent-dtsx” package. As before, click “No” in the “Script Task 4” prompt. The OnError event handler for the “Parent” package is next executed after confirming the message box for the OnError event handler for “Script Task 4”, “sequence container 1”, and “Precedence” package.

Here are a few interesting things to point out:

First, in the parent-child SSIS design pattern, error events bubble from “Precedence. DTSX “at the” bottom “of the SSIS package (Script Task 4) to the” top “of the “Parent.

The Precedence. DTSX package is as if it were in scope of the Parent. DTSX package’s Execute package task.

Second, the Error events originally generated by “Script Task 4” are still configured with the original variable values. When an Error event bubbles, the values of the ErrorCode, ErrorDescription, and SourceName SSIS variables remain static — even if it bubbles from the child package to the parent.

This behavior applies to all SSIS task events, not just OnError events.

One last thing (DisableEventHandlers property)

You might be wondering, in the parent-child SSIS design pattern, do we need to manage events at the child level? Can we manage all events only at the parent level?

The answer is: it depends on the event management design that fits your business, and even if you allow most events to bubble up to the top of the parent package, there are valid use cases that can capture individual SSIS tasks and container events within the scope of the child package. The key, I believe, is that you now understand more about the flexibility of configuring these events. You now have options, and all of them are good.

The last thing I want to discuss is the DisableEventHandlers property.

Executable files in SSIS — in other words, SSIS tasks and containers — both have the DisableEventHandlers property.

The DisableEventHandlers property is inherited by the task or container-scoped executable. Its default value is False. It is important to note that not all attributes are true. MaximumErrorCount and ForceExecutionResult are not inherited.

Click “precedence. DTSX” to control the flow, press “F4” to display the packet properties, and change the DisableEventHandlers property to True.

Click on the blank area of the “parent.dtsx” package control flow to make sure “parent.dtsx” is selected. Press “F5” to execute the “Parent. DTSX “package. Again, click the “No” button in the “Script Task 4” prompt. Next, the message box for the “Parent” OnError event handler is displayed.

Why is that? Because the “Precedence. DTSX “SSIS package event handler is now disabled.

Also, you might want to isolate certain events and stop bubbling within a subpackage event handler.

conclusion

In this article, we focus on the behavior of events. And demonstrated two ways to change the default behavior of event bubbling:

  • DisableEventHandlers using the DisableEventHandlers property;

  • Cancel event bubbling by manipulating the value of the System::Propagate SSIS variable within the event handler using a script task.

It also introduces simple custom errors, as well as the parent-child pattern, which examines the behavior of events within the parent-child pattern.

Note: The packet Name Package1 of Precedence. DTSX can be modified using the Name attribute in the packet property to ensure that the Precedence Name corresponds to the packet Name.