SSIS Package的Executable存在层次结构,例如Package位于层次结构的最顶层,Root Level;Container是其中包含的Executable(Task 或 Container)的Parent Level。如果Event 发生在Child Level,首先会被Child Level的Event Handler捕获,并处理;如果Child Level没有相应的Event handler,那么该Event会向上传递给其Parent Executable,由其Parent Executable的Event Handler来处理。Event依次向上传递的过程就是Propagate。
MSDN对Propagate的官方定义是:
Propagate is a system variable for event handlers, indicates whether the event is propagated to a higher level event handler.
Note:The value of the Propagate variable is disregarded during the validation of the package. If you set Propagate to False in a child package, this does not prevent an event from propagating up to the parent package.
1,OnError 事件具有Propagate特性
当Child Event Handler 处理OnError事件之后,OnError事件仍然会向上传递,直到最顶层的Package。
例如,在Package 和 Executable级别分别创建了OnError Event Handler,如下图:
下图是Executable级别的OnError Event Handler,其成功执行一个Task。
但是,OnError事件继续向上传递,被Package级别的OnError Event Handler捕获并处理之后,Package仍然报错:Package execution completed with error.
Event 的 Propagate 过程类似“冒泡”,从触发的Task开始,向上传递到最顶层的Executable,最顶层的Executable是 Package 本身。如果在 package 级别定义了一个Event handler,那么每当Event 发生在 package 中,都会触发该 Event Handler。如果Package使用Execute Package Task来执行子package,那么也会发生相同的过程,子package的Event会propagate到父package中。
2,关闭Event的Propagate
如果希望关闭Event的propagate过程,可以在子 Taske 的Event Handler中,将系统变量Propagate设置为false,这样,Event将不会向上传递,而只触发当前Task的Event Handler。系统变量Propagate的默认值是True。
step1,在Variables 窗体中,点击Grid Options,打开Variable Grid Options 窗体,勾选Show system variables
step2,在 Variables 窗体中,找到 Propagate 系统变量(Scope是OnError),将Value设置为False
3,当Event的Propagate 关闭之后,Event只会被触发的Task的Event Handler捕获并处理,而不会向上传递。
再次执行Package,由于OnError 事件是被child Execute SQL Task触发的,其Event handler捕获并处理该OnError事件
而Package级别的Event Handler并没有捕获到OnError事件,package的执行结果是Success。
Appendix:
引用《Integration Services (SSIS) Event Handlers》:
At run time, executables (packages and Foreach Loop, For Loop, Sequence, and task host containers) raise events. For example, an OnError event is raised when an error occurs. 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.
The following diagram shows a simple package that has a For Loop container that contains one Execute SQL task.
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 following diagram shows the sequence of calls that causes the OnError event handler for the package to execute.
Event handlers are members of an event handler collection, and all containers include this collection. If you create the package using SSIS Designer, you can see the members of the event handler collections in the Event Handlers folders on the Package Explorer tab of SSIS Designer.
参考doc: