stay SSIS in ,Package yes Task An ordered combination of components , It has a hierarchical structure ,Package At the top of the hierarchy (Root Level), For the parent-child package structure , Parent package (Parent Package) adopt Execute Package Task Component calls other Package, The called Package It's Zibao , The parent package is the upper level of the child package , Topmost Package, At the top of the hierarchy , It's called root bag (Root Package); Containers (Container) Component contains other Task Components , Containers are contained Task The parent level of the component (Parent Level);Task Components are at the bottom of the hierarchy , At the leaf level (Leaf Level). The event is along the Package The hierarchy of is passed up .

stay Package in , every last Task A component is an executable program (Executable), All the data processing tasks are done by Task Components complete . stay Package function (runtime) when ,SSIS The engine is for monitoring and tracking Task The running state of the component , Pre created 12 A system event (Event), These events are all in Package Runtime (runtime) By executable program (Executable) The trigger , Each event generates a corresponding message , Used to describe Executable Operating state , For development engineers to Package Debugging and performance tuning . Once an event is triggered ,SSIS The corresponding event handler is executed (Event Handler),SSIS A default event handler is created for each event , The naming convention is :On+EventName, Users can create custom event handlers , In order to expand Package The function of , send Package Easier to manage at run time , To complete the data processing task . stay ETL In development , The most common event is an error (Error) event , The incident is Executable Trigger when there is an error in the run , The corresponding event handler is OnError.

stay Package In the hierarchy of , Event handling has the ability to pass up (Propagate) Characteristics of . It happened in Task Component events , First of all, it will be Task Component's Event Handler Capture and processing ; If it's time to Task Component not created Event Handler, that SSIS Take this Event Up to the level of its father Executable, At the level of his father Event Handler To deal with it ; If it's time to Executable Yes Event Handler, So it's up to you Event Handler Responsible for responding to and handling the incident . Events go up in turn , Until the event is dealt with , Or it can be passed to the top layer and processed by default , The top level of event passing up is the root package (Root Package).

quote MSDN Examples of official documents ,Package The hierarchy of is shown in the figure below :

In hierarchy , If the corresponding Task Component has no event handler defined , The process of event upward transmission is shown in the figure below :

notes : The condition for passing events up is that no custom event handler has been created , By default , The diagram is wrong , The incident was Event Handler After processing , Will continue to pass up , I'll explain in detail below .

MSDN The illustration is given :

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 property of passing events up in turn , It's by the event handler (Event Handler) The system variable of Propagate The control of the , Variable Propagate The default value of is True, It means , By default , The event will be passed to the upper level Event Handler Intermediate processing . One exception is in the parent-child package structure , Son Package During package validation (Validation) when , Regardless of the package Propagate How to set the value of a variable , Will pass validation events to the parent package , The parent package continues to perform validation .

At present Task The component must create an event handler , To view and modify variables Propagate Value . If you put the event handler (Event Handler) Of Propagate The variable is set to False, Then the event will only be processed and responded to by the current event handler , Will not be passed to the upper level event handler . however , If not for “ Cause trouble ” Of Task Component creates an event handler (Event Handler), So events always go up , Until the event handler responds , If Package There are no event handlers defined in the hierarchy of , So the event is ultimately wrapped by the root (Root Package) Default processing .

One , Error event handler (OnError) Pass up

By default , At present Task Component's event handler (Error) After the event is handled ,SSIS The engine still passes error events to the upper event handler , Up to the top of the package hierarchy , Here's the picture , stay ChildPackage Of Package Level and Executable At the same level, we created OnError Event handler ,Executable Level is child Execute SQL Task:

perform Package, stay child Execute SQL Task Trigger error events in , By the Task Event handlers capture and handle , The picture below is Executable Grade OnError Event handler , It successfully implemented a Task:

however , Error events don't stop , It's going to go up , By his immediate superior , That is to say Package Grade OnError Event handler capture , Here's the picture , From son Task After the error event of the component is handled by the parent ,Package Still reporting a mistake , The error message is :Package execution completed with error.

The error event handler takes the error event (Error) Pass up (Propagate) The process is similar to “ Bubbling ”, From the “ Cause trouble ”Task Component start , Step by step up to the top level of the executable program (Executable), Topmost Executable yes Package In itself . It means , If in Package Level defines an error event handler (OnError), whenever Package Any one of Task Component triggers error events (Error), It will trigger Package Level error event handler . In the parent-child structure , If the father Package adopt Execute Package Task Callers Package, that , The same process occurs for error events , Son Package The error event for is passed up (Propagate) To the father Package in .

Two , Disable up passing of error events

If you want to disable the up passing of events , Can be in Task Component's event handler , Put the system variable Propagate Set to False, such , Events will no longer be passed up , Trigger only the current Task Component's event handler , Only in Task After the event handler is created in the component , To modify system variables Propagate The default value of .

1, Modify system variables Propagate The default value of

step1, Render system variables

open Event Handlers Tab, stay Variables In form , Click the grid option (Grid Options) Button , open Variable Grid Options forms , stay Filter Check the options "Show system variables", Click on “OK”, Back to Variables forms :

step2, Set up Propagate Value

stay Variables In form , find Propagate System variables (Scope yes OnError), hold Value Set to False

2, Disable up passing of event handling

Ban Task Component's event handling is passed up (Propagate) After features , At present Task Events triggered in a component , Only by the present Task Component event handlers capture and handle , Instead of passing it up to the top Task Component's event handler .

Re execution Package, Because the error event is child Execute SQL Task The trigger , Its event handler automatically captures and processes the Error event :

and Package Level event handler did not catch Error event ,Package No implementation OnError Event handler , The final result of implementation is :Package execution completed with success.

3、 ... and , No event handler was created

If not for Task Component creates an event handler (Event Handler), Then events always start from the present Task Component passes up . The user creates the event handler , It doesn't mean that , You need to add Task Components , Do data processing tasks , Empty event handlers are allowed . In an empty event handler, put the system variable Propagate Set to False, Then events will not pass up , And it won't be handled explicitly , Error messages will still be SSISDB Record , But it is strongly recommended not to .

Reference documents :

Integration Services (SSIS) Event Handlers

System Variables

Security1:Create Login More articles about

  1. Security2:Create User

    User Used to access the DB Users based on logins in master (This is the most common type of user.) User based on a ...

  2. SQL Server Server information backup ( One )--login New script backup

    Preface If your business uses SQL Server Database image is disaster recovery technology . Then you must have done to create a new image before the image switch Login, And if Login Different passwords , To modify the string of the linked database , After switching, you still need to re grant permissions to the database ...

  3. SQL Server ->> EXECUTE AS LOGIN/USER and Revert expression

    EXECUTE AS LOGIN/USER and Revert Expressions are all derived from SQL Server 2005 There is .Revert Is used to switch the execution context of the current process and return to the previous EXECUTE AS Before the statement occurs ...

  4. SQLServer Of Login Migration scripts

    background : The company's data are provided by SQLServer2008 R2 To upgrade to SQLServer2012, And configured AlwaysOn, This script is used to transfer the Login Migrate to secondary nodes . 1. Execute the following script at the master node to create the stored procedure : U ...

  5. [ turn ]jQuery Popup Login and Contact Form

    In this paper, from :http://www.formget.com/jquery-popup-form/ Pop up forms are the smart way to present your site. ...

  6. SQLServer User and Login Tips

    use master IF EXISTS (SELECT * FROM sys.databases WHERE name = 'gpdb83sp')BEGIN DROP DATABASE gpdb83 ...

  7. SSAS Issue error handling method Login failed for user 'NT Service\MSSQLServerOLAPService' 28000

    Create login and grant access: Open up SQL Server Management Studio [login to the database engine]&g ...

  8. SQL - 1. distinguish login、user、schema and role

        Login Login  yes Server First level concept , Sign in Server Proof of , For example Server_A There's a database on DA, So if you want to access the database DA, The first step is to log in to Hosting This data ...

  9. How to add the ApplicationPoolIdentity to a SQL Server Login

    The ApplicationPoolIdentity is a virtual account in Windows that is dynamically generated when the a ...

Random recommendation

  1. java Basic encryption algorithm

    ordinary java Encryption algorithms include : BASE64 Strictly speaking , It belongs to coding format , Not encryption algorithms MD5(Message Digest algorithm 5, Information digest algorithm ) SHA(Secure Hash Algor ...

  2. ASP.NET MVC: Form authentication and role rights management example

    ASP.NET MVC establish ASP.NET On the basis of , quite a lot ASP.NET Characteristics of ( Such as form authentication . Membership ) stay MVC Can be used directly in . This article aims to provide reference code , It won't involve too much theoretical knowledge in this area . Ben ...

  3. adopt ping Make sure the network card mtu

    Use ping Test to determine the best network card MTU Methods MTU ( Maximum Transmission Unit , Maximum transmission unit ) It refers to the maximum amount of data that can be transmitted in a certain layer protocol . When a packet exceeds MTU data ...

  4. nsstring String restructuring

    // //  main.m //  05- String restructuring // //  Created by apple on 14-3-20. //  Copyright (c) 2014 year apple. All ri ...

  5. [RM HA3] Zookeeper stay RM HA Application

    [RM HA3] Zookeeper stay RM HA Application RM HA(ResourceManager  HighAvailability) Use in Zookeeper Where ZKRMStateStore and Z ...

  6. [C Language - 14] Base number

    Base number Binary system int number = 0B1010; There is no binary output format   octal     int number2 = 07613;     printf("number2 = %o\ ...

  7. JavaScript--- Network programming (7)-Dom Model ( The hierarchical relationship between nodes , The increasing of the node 、 Delete 、 Change )

    Use the hierarchical relationship between nodes to get nodes : Last section 3 The way to get in : * ※※ One . Absolutely get , Fetch element 3 Ways of planting :-Element * 1.getElementById(): Through... In the label id Property value to get the label pair ...

  8. About easyUI Of datebox And the loss of focus event is click、blur etc.

    Want to implement the date box easyui-datebox Enter the value manually , Manual input loses focus to judge whether the input value is legal for example <input id="txtDate" type="text" ...

  9. java -ui First experience of Automation

    Let's talk about this ui Automated environment building , And the most elementary operation of opening a web page Speaking of ui automation , Think about the year before last when I began to contact and learn it , How to say, whether it's pc still app,ios still android, It's really amazing and gorgeous , however ...

  10. Oracle Statistics collection

    Website reference : https://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_stats.htm#CIHBIEII https://docs.ora ...