Integration Services (SSIS) 事件处理程序Integration Services (SSIS) Event Handlers

适用对象:是SQL Server,包含 Linux 版 是Azure SQL 数据库 是Azure Synapse Analytics (SQL DW) 否并行数据仓库 APPLIES TO: yesSQL Server, including on Linux yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

在运行时,可执行文件(包以及 Foreach 循环容器、For 循环容器、序列容器和任务宿主容器)会引发事件。At run time, executables (packages and Foreach Loop, For Loop, Sequence, and task host containers) raise events. 例如,错误发生时会引发 OnError 事件。For example, an OnError event is raised when an error occurs. 可以为这些事件创建自定义事件处理程序,以扩展包的功能并使包在运行时更容易管理。You can create custom event handlers for these events to extend package functionality and make packages easier to manage at run time. 事件处理程序可以执行诸如下列任务:Event handlers can perform tasks such as the following:

  • 当包或任务运行完成时清除临时数据存储。Clean up temporary data storage when a package or task finishes running.

  • 在包运行前检索系统信息,以便评估资源可用性。Retrieve system information to assess resource availability before a package runs.

  • 在引用表中的查找失败时刷新表中的数据。Refresh data in a table when a lookup in a reference table fails.

  • 当发生错误或警告时,或者当任务失败时,发送电子邮件。Send an e-mail message when an error or a warning occurs or when a task fails.

如果事件没有事件处理程序,则将该事件提升到包中容器层次结构中的上一级容器。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.

以下关系图显示具有 For 循环容器的简单包,该容器包含一个执行 SQL 任务。The following diagram shows a simple package that has a For Loop container that contains one Execute SQL task.

包、For 循环、任务主机和执行 SQL 任务Package, For Loop, task host, and Execute SQL task

只有包具有事件处理程序,用于处理其 OnError 事件。Only the package has an event handler, for its OnError event. 如果执行 SQL 任务运行时发生错误,包的 OnError 事件处理程序就会运行。If an error occurs when the Execute SQL task runs, the OnError event handler for the package runs. 以下关系图显示导致包的 OnError 事件处理程序执行的调用序列。The following diagram shows the sequence of calls that causes the OnError event handler for the package to execute.

事件处理程序流Event handler flow

事件处理程序是事件处理程序集的成员,所有容器都包含此事件处理程序集。Event handlers are members of an event handler collection, and all containers include this collection. 如果使用 SSISSSIS 设计器创建包,就可以在 设计器的 “包资源管理器” 选项卡上的 “事件处理程序” SSISSSIS 文件夹中看到事件处理程序集的成员。If you create the package using SSISSSIS Designer, you can see the members of the event handler collections in the Event Handlers folders on the Package Explorer tab of SSISSSIS Designer.

可以采用下列方法配置事件处理程序容器:You can configure the event handler container in the following ways:

  • 指定事件处理程序的名称和说明。Specify a name and description for the event handler.

  • 指示事件处理程序是否运行,事件处理程序失败时包是否失败,以及事件处理程序失败前可发生的错误数。Indicate whether the event handler runs, whether the package fails if the event handler fails, and the number of errors that can occur before the event handler fails.

  • 指定返回执行结果,而不是事件处理程序在运行时返回的实际执行结果。Specify an execution result to return instead of the actual execution result that the event handler returns at run time.

  • 指定事件处理程序的事务选项。Specify the transaction option for the event handler.

  • 指定事件处理程序使用的日志记录模式。Specify the logging mode that the event handler uses.

事件处理程序内容Event Handler Content

创建事件处理程序与生成包类似;事件处理程序具有多个任务和容器,这些任务和容器按照一定顺序组织成控制流,事件处理程序也可以包含多个数据流。Creating an event handler is similar to building a package; an event handler has tasks and containers, which are sequenced into a control flow, and an event handler can also include data flows. SSISSSIS 设计器包含 “事件处理程序” 选项卡,用于创建自定义事件处理程序。The SSISSSIS Designer includes the Event Handlers tab for creating custom event handlers.

还可以采用编程方式创建事件处理程序。You can also create event handlers programmatically. 有关详细信息,请参阅 以编程方式处理事件For more information, see Handling Events Programmatically.

运行时事件Run-Time Events

下表列出 Integration ServicesIntegration Services 提供的事件处理程序,并介绍导致事件处理程序运行的运行时事件。The following table lists the event handlers that Integration ServicesIntegration Services provides, and describes the run-time events that cause the event handler to run.

事件处理程序Event handler 事件Event
OnErrorOnError OnError 事件的事件处理程序。The event handler for the OnError event. 此事件在发生错误时由可执行文件引发。This event is raised by an executable when an error occurs.
OnExecStatusChangedOnExecStatusChanged OnExecStatusChanged 事件的事件处理程序。The event handler for the OnExecStatusChanged event. 此事件在其执行状态更改时由可执行文件引发。This event is raised by an executable when its execution status changes.
OnInformationOnInformation OnInformation 事件的事件处理程序。The event handler for the OnInformation event. 此事件在可执行文件的验证和执行期间引发以报告信息。This event is raised during the validation and execution of an executable to report information. 此事件仅传递信息,不传递错误或警告。This event conveys information only, no errors or warnings.
OnPostExecuteOnPostExecute OnPostExecute 事件的事件处理程序。The event handler for the OnPostExecute event. 此事件由可执行文件在其运行完成后立即引发。This event is raised by an executable immediately after it has finished running.
OnPostValidateOnPostValidate OnPostValidate 事件的事件处理程序。The event handler for the OnPostValidate event. 此事件由可执行文件在其验证完成时引发。This event is raised by an executable when its validation is finished.
OnPreExecuteOnPreExecute OnPreExecute 事件的事件处理程序。The event handler for the OnPreExecute event. 此事件由可执行文件在其运行前引发。This event is raised by an executable immediately before it runs.
OnPreValidateOnPreValidate OnPreValidate 事件的事件处理程序。The event handler for the OnPreValidate event. 此事件由可执行文件在其验证开始时引发。This event is raised by an executable when its validation starts.
OnProgressOnProgress OnProgress 事件的事件处理程序。The event handler for the OnProgress event. 此事件由可执行文件在其完成可度量的进度时引发。This event is raised by an executable when measurable progress is made by the executable.
OnQueryCancelOnQueryCancel OnQueryCancel 事件的事件处理程序。The event handler for the OnQueryCancel event. 此事件由可执行文件引发,以确定它是否应停止运行。This event is raised by an executable to determine whether it should stop running.
OnTaskFailedOnTaskFailed OnTaskFailed 事件的事件处理程序。The event handler for the OnTaskFailed event. 此事件由任务在其失败时引发。This event is raised by a task when it fails.
OnVariableValueChangedOnVariableValueChanged OnVariableValueChanged 事件的事件处理程序。The event handler for the OnVariableValueChanged event. 此事件在变量值更改时由可执行文件引发。This event is raised by an executable when the value of a variable changes. 此事件由此变量的可执行文件引发。The event is raised by the executable on which the variable is defined. 如果将变量的 RaiseChangeEvent 属性设置为 False,则不引发此事件。This event is not raised if you set the RaiseChangeEvent property for the variable to False. 有关详细信息,请参阅 Integration Services (SSIS) 变量For more information, see Integration Services (SSIS) Variables.
OnWarningOnWarning OnWarning 事件的事件处理程序。The event handler for the OnWarning event. 此事件在发生警告时由可执行文件引发。This event is raised by an executable when a warning occurs.

在包中添加事件处理程序Add an event handler to a package

在运行时,容器和任务引发事件。At run time, containers and tasks raise events. 您可以创建自定义事件处理程序,这些程序在事件被引发时运行工作流,以对事件做出响应。You can create custom event handlers that respond to these events by running a workflow when the event is raised. 例如,您可创建一个事件处理程序,在任务失败时发送电子邮件。For example, you can create an event handler that sends an e-mail message when a task fails.

事件处理程序与包类似。An event handler is similar to a package. 事件处理程序可以像包一样为变量提供作用域,并且包含控制流和可选数据流。Like a package, an event handler can provide scope for variables, and includes a control flow and optional data flows. 您可以为包、Foreach 循环容器、For 循环容器和序列容器以及所有的任务生成事件处理程序。You can build event handlers for packages, the Foreach Loop container, the For Loop container, the Sequence container, and all tasks.

您可以使用 设计器中的 “事件处理程序” SSISSSIS 选项卡的设计图面来创建事件处理程序。You create event handlers by using the design surface of the Event Handlers tab in SSISSSIS Designer.

“事件处理程序” 选项卡活动时, 设计器中的工具箱的 “控制流项” “维护计划中的任务” SSISSSIS 节点包含用于生成事件处理程序中控制流的任务和容器。When the Event Handlers tab is active, the Control Flow Items and Maintenance Plan Tasks nodes of the Toolbox in SSISSSIS Designer contain the task and containers for building the control flow in the event handler. “数据流源”“转换”“数据流目标” 节点包含用于生成事件处理程序中数据流的数据源、转换和目标。The Data Flow Sources, Transformations, and Data Flow Destinations nodes contain the data sources, transformations, and destinations for building the data flows in the event handler. 有关详细信息,请参阅 Control FlowData FlowFor more information, see Control Flow and Data Flow.

“事件处理程序” 选项卡也包含 “连接管理器” 区域,在这里可创建并修改事件处理程序用来连接到服务器和数据源的连接管理器。The Event Handlers tab also includes the Connections Managers area where you can create and modify the connection managers that event handlers use to connect to servers and data sources. 有关详细信息,请参阅 创建连接管理器For more information, see Create Connection Managers.

在“事件处理程序”选项卡上添加事件处理程序Add an event handler on the Event Handlers tab

  1. SQL Server Data Tools (SSDT)SQL Server Data Tools (SSDT)中,打开包含所需包的 Integration ServicesIntegration Services 项目。In SQL Server Data Tools (SSDT)SQL Server Data Tools (SSDT), open the Integration ServicesIntegration Services project that contains the package you want.

  2. 在解决方案资源管理器中,双击该包将其打开。In Solution Explorer, double-click the package to open it.

  3. 单击 “事件处理程序” 选项卡。Click the Event Handlers tab.

    带有事件处理程序的设计图面的屏幕截图Screenshot of design surface with event handler

    在事件处理程序中创建控制流和数据流类似于在包中创建控制流和数据流。Creating the control flow and data flows in an event handler is similar to creating the control flow and data flows in a package. 有关详细信息,请参阅 Control FlowData FlowFor more information, see Control Flow and Data Flow.

  4. “可执行文件” 列表中,选择要为其创建事件处理程序的可执行文件。In the Executable list, select the executable for which you want to create an event handler.

  5. “事件处理程序” 列表中,选择要生成的事件处理程序。In the Event handler list, select the event handler you want to build.

  6. 单击 “事件处理程序” 选项卡的设计图面上的链接。Click the link on the design surface of the Event Handler tab.

  7. 将控制流项添加到事件处理程序,并且通过将优先约束从一个控制流项拖到另一控制流项,使用该约束来连接这些控制流项。Add control flow items to the event handler, and connect items using a precedence constraint by dragging the constraint from one control flow item to another. 有关详细信息,请参阅 Control FlowFor more information, see Control Flow.

  8. 还可以根据需要添加数据流任务,并且在 “数据流” 选项卡的设计图面上,为事件处理程序创建数据流。Optionally, add a Data Flow task, and on the design surface of the Data Flow tab, create a data flow for the event handler. 有关详细信息,请参阅 Data FlowFor more information, see Data Flow.

  9. “文件” 菜单上,单击 “保存选定项” 以保存包。On the File menu, click Save Selected Items to save the package.

设置事件处理程序的属性Set the properties of an event handler

可以在 “属性” SQL Server Data Tools (SSDT)SQL Server Data Tools (SSDT) 窗口中设置属性,或以编程方式设置属性。You can set properties in the Properties window of SQL Server Data Tools (SSDT)SQL Server Data Tools (SSDT) or programmatically.

有关如何在 SQL Server Data Tools (SSDT)SQL Server Data Tools (SSDT)中设置这些属性的信息,请参阅 设置任务或容器的属性For information about how to set these properties in SQL Server Data Tools (SSDT)SQL Server Data Tools (SSDT), see Set the Properties of a Task or Container.

有关如何以编程方式设置这些属性的信息,请参阅 DtsEventHandlerFor information about programmatically setting these properties, see DtsEventHandler.

有关如何向包中添加事件处理程序的信息,请参阅在包中添加事件处理程序For information about how to add an event handler to a package, see Add an Event Handler to a Package.