SSIS event-based upstream transmission (detailed)

SSIS event-based upstream transmission (detailed)

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

In a Package, each Task component is an Executable program, and all data processing tasks are completed by the Task component. During Package running (runtime), The SSIS engine Creates 12 system events in advance to monitor and track the running status of the Task component ), these events are all triggered by the Executable program (Executable) during the Package runtime. Each event generates a message to describe the running status of the Executable, for developers to debug and optimize the Package. Once an Event is triggered, SSIS executes the corresponding Event Handler. SSIS creates a default Event Handler for each Event. The naming convention is: On + EventName, you can create a custom event handler to expand the Package function, making it easier to manage the Package at run time to complete data processing tasks. In ETL development, the most common event is an Error event, which is triggered when an Error occurs during Executable running and the corresponding event handler is OnError.

In the Package hierarchy, event processing has the Propagate feature. Events that occur in the Task component are first captured and processed by the Event Handler of The Task component. If the Task component does not create an Event Handler, SSIS then transmits the Event to the parent-level Executable, which is handled by the parent-level Event Handler. If the Executable has an Event Handler, the Event Handler is responsible for responding to and processing the Event. Events are sequentially passed up until the events are processed, or passed to the top layer for default processing. The top layer of the event to be passed up is the Root Package ).

An example of the Package hierarchy is shown in the following link:

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

Note:The Event is passed up only when no custom Event Handler is created. By default, this graph is incorrect. After the Event is handled by Event Handler, the Event will be passed up, I will explain it in detail below.

MSDN describes 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 features that pass events in turn are controlled by the Propagate system variable of the Event Handler. The default value of the variable Propagate is True, which means that, by default, the Event will be passed to the Event Handler at the upper level for processing. One exception is that in the parent-child Package structure, when the sub-Package performs Package Validation, how does one set the value of the Propagate variable of the non-pipe Package, the verification event is passed to the parent package, and the parent package continues to perform the verification.

The current Task component must create an event handler to view and modify the value of the variable Propagate. If you set the Propagate variable of the Event Handler to False, the Event will only be processed and responded by the current Event Handler, it will not be passed to the event handler at the upper level. However, if no Event Handler is created for the Task component of the "accident", the Event is always passed up until the Event Handler responds, if no event handler is defined in the Package hierarchy, the event is eventually processed by the Root Package by default.

1. The error event handler (OnError) is passed up

By default, after the Error event is processed in the event handler of the current Task component, the SSIS engine still transmits the Error event to the event handler at the upper layer, until the top layer of the Package hierarchy, for example, the OnError event handler is created at the Package level and Executable level of ChildPackage, And the Executable level is child Execute SQL Task:

Run the Package and trigger an error event in the child Execute SQL Task. It is captured and processed by the event processing program of the Task. It is an OnError event processing program at the Executable level and successfully runs a Task:

However, the error event does not stop, but continues to be passed up and is directly captured by its upper-level, namely, the Package-level OnError event handler. For example, after an error event from the Child Task component is processed by the parent, the Package still reports an error. The error message is Package execution completed with error.

The Error handling program transfers an Error event (Propagate) up to a process similar to "bubble", starting from the "accident" Task component that triggers the Error event, the top-level Executable program is passed up to the top-level, and the top-level Executable is the Package itself. This means that if an Error event handler (OnError) is defined at the Package level, each time a Task component in the Package triggers an Error event (Error ), the Package-level error event handler will be triggered eventually. In the Parent-Child Package structure, if the parent Package uses the Execute Package Task to call the sub-Package, the error event will be the same process, and the error event of the sub-Package will be passed up (Propagate) to the parent Package.

2. Disable the upload of error events

If you want to disable the event upward transfer process, you can set the system variable Propagate to False in the event processing program of the Task component. In this way, the event will not be passed up, only the event handler of the current Task component is triggered. The default value of the system variable Propagate can be modified only after an event handler is created in the Task component.

1. Modify the default value of the system variable Propagate.

Step 1: Present System Variables

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

Step 2: set the value of Propagate

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

2. disable event processing to pass up

After the Propagate feature of event processing of the Task component is disabled, events triggered in the current Task component will only be captured and processed by the event processing program of the current Task component, the event handler that is not passed to the upper-layer Task component.

Run Package again. Because the Error event is triggered by the child Execute SQL Task, the event handler automatically captures and processes the Error event:

The Package-level event handler does not capture the Error event, and the Package does not execute the OnError event handler. The final execution result is: Package execution completed with success.

3. No event handler is created

If you have not created an Event Handler for the Task component, the Event is always passed up from the current Task component. Creating an event handler does not mean that you need to add the Task component in it for data processing tasks. Empty event handlers are allowed. If the system variable Propagate is set to False in an empty event handler, the event will not be passed up or explicitly processed, and the error message will still be recorded by SSISDB, however, we strongly recommend that you do not do this.

Reference:

Integration Services (SSIS) Event Handlers

System Variables

The above SSIS-based event transfer-up (detailed description) is all the content shared by Alibaba Cloud. I hope you can give us a reference and support for the customer's home.