Shulou(Shulou.com)05/31 Report--

This article mainly introduces the example analysis based on the upward transmission of SSIS events, which has a certain reference value, and interested friends can refer to it. I hope you will gain a lot after reading this article.

In SSIS, Package is an ordered combination of Task components and has a hierarchical structure. Package is at the top level of the hierarchy (Root Level). For parent-child package structure, the parent package (Parent Package) invokes other Package through the Execute Package Task component, the called Package is the child package, the parent package is the upper level of the child package, and the top-most Package is at the top level of the hierarchy, called Root Package. The Container component contains other Task components, and the container is the parent level (Parent Level) of the included Task component; the Task component is at the bottom of the hierarchy and is at the Leaf Level level. Events are passed up the Package hierarchy.

In Package, each Task component is an executable program (Executable), and all data processing tasks are completed by the Task component. When Package is running (runtime), SSIS engine creates 12 system events (Event) in advance in order to monitor and track the running status of Task components. These events are triggered by executable program (Executable) while Package is running (runtime). Each event will generate a corresponding message to describe the running status of Executable for development engineers to debug and tune the performance of Package. Once an event is triggered, SSIS will execute the corresponding event handler (Event Handler). SSIS has created a default event handler for each event. The naming convention is: On+EventName, users can create custom event handlers to extend the functions of Package and make Package easier to manage at run time to complete data processing tasks. In ETL development, the most common event is the Error event, which is triggered when an error occurs in the Executable run, and the corresponding event handler is OnError.

In the Package hierarchy, event handling has the feature of Propagate. Events that occur in the Task component are first captured and handled by the Event Handler of the Task component; if the Task component does not create an Event Handler, then the SSIS passes the Event up to its parent-level Executable, which is handled by its parent-level Event Handler; if the Executable has an Event Handler, the Event Handler is responsible for responding to and handling the event. Events are passed up in turn until the event is handled, or passed to the top level, which is handled by default, and the top level where the event is passed is the Root Package.

Citing the example of the official MSDN document, the hierarchical structure of Package is shown in the following figure:

In the hierarchy, if the corresponding Task component does not define an event handler, the process of passing the event up is shown in the following figure:

Note: the condition for the event to be passed up is that no custom event handler is created. By default, the icon is incorrect, and after the event is processed by Event Handler, it will continue to be passed upward, which I will explain in more detail below.

MSDN explains the illustration:

If an event has no event handler, the event is raised to the next container up the container hierarchy in a package. If this container has an event handler, the event handler runs in response to the event. If not, the event is raised to the next container up the container hierarchy. Only the package has an event handler, for its OnError event. If an error occurs when the Execute SQL task runs, the OnError event handler for the package runs.

The feature of passing events up in turn is controlled by the system variable Propagate of the event handler (Event Handler), and the default value of the variable Propagate is True, which means that, by default, the event will be passed to the upper-level Event Handler for processing. One exception is that in the parent-child package structure, when the child Package performs package validation (Validation), regardless of how the value of the Propagate variable of the child package is set, the validation event is passed to the parent package, and the parent package continues to perform validation.

The current Task component must create an event handler to view and modify the value of the variable Propagate. If the Propagate variable of the event handler (Event Handler) is set to False, the event will only be handled and responded to by the current event handler and will not be passed to the upper-level event handler. However, if no event handler (Event Handler) is created for the "trouble-causing" Task component, the event is always passed up until it is responded to by the event handler, and if the Package hierarchy does not define any event handlers, then the event is eventually handled by default by the Root Package.

First, the error event handler (OnError) is passed up

By default, after the error (Error) event is handled in the current Task component event handler, the SSIS engine still passes the error event to the upper event handler until the top level of the package hierarchy, as shown in the following figure, creates OnError event handlers at the Package level and Executable level of ChildPackage, respectively, and the Executable level is child Execute SQL Task:

Execute Package, which triggers an error event in child Execute SQL Task, which is captured and handled by the event handler of the Task. The following figure shows an Executable-level OnError event handler that successfully executes a Task:

However, the error event does not stop, but continues to be passed upward and is captured by its direct superior, that is, the Package-level OnError event handler. As shown in the following figure, after the error event from the child Task component is handled by the parent, Package still reports an error, and the error message is: Package execution completed with error.

The process of an error event handler passing an error event (Error) up (Propagate) is similar to "bubbling", starting with the "causing" Task component that triggers the error event, and progressively up to the top-level executable (Executable), with the top-level Executable being the Package itself. This means that if an error event handler (OnError) is defined at the Package level, each time any Task component in the Package triggers an error event (Error), an error event handler at the Package level will eventually be triggered. In the parent-child package structure, if the parent Package invokes the child Package through Execute Package Task, the same process occurs for the error event, and the error event for the child Package is Propagate to the parent Package.

Second, disable the upward transmission of error events

If you want to disable the upward passing of events, you can set the system variable Propagate to False in the event handler of the Task component, so that events will no longer be passed up, only the event handler of the current Task component will be triggered, and the default value of the system variable Propagate can be changed only after an event handler has been created in the Task component.

1. Modify the default value of the system variable Propagate

Step1, rendering system variabl

Open Event Handlers Tab, in the Variables form, click the Grid Options button, open the Variable Grid Options form, check "Show system variables" in the Filter options, click "OK", and return to the Variables form:

Step2, sets the value of Propagate

In the Variables form, find the Propagate system variable (Scope is OnError) and set Value to False

2. Disable the upward delivery of event handling

When the Propagate feature of event handling of the Task component is disabled, events triggered in the current Task component are only captured and handled by the event handler of the current Task component, not passed to the event handler of the upper Task component.

Execute Package again, and because the error event is triggered by child Execute SQL Task, its event handler automatically captures and handles the Error event:

However, Package-level event handlers do not capture Error events, Package does not execute OnError event handlers, and the final result is Package execution completed with success.

Third, no event handlers are created

If no event handler (Event Handler) is created for the Task component, the event is always passed up from the current Task component. Just because users create event handlers does not mean that they need to add Task components to do data processing tasks. Empty event handlers are allowed. If you set the system variable Propagate to False in an empty event handler, events will not be passed up and will not be handled explicitly, and error messages will still be logged by SSISDB, but it is highly recommended that you do not do so.

Thank you for reading this article carefully. I hope the article "sample Analysis based on the upward Transmission of SSIS events" shared by the editor will be helpful to you. At the same time, I also hope that you will support and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.