Integration Services (SSIS) 事件處理常式Integration Services (SSIS) Event Handlers

在執行階段,可執行檔 (封裝和「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. 如需詳細資訊,請參閱控制流程資料流程For 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. 如需詳細資訊,請參閱控制流程資料流程For 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. 如需詳細資訊,請參閱控制流程For 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. 如需詳細資訊,請參閱資料流程For 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.