DisableEventHandlers property in SSIS package

Event Handlers is one of the most useful and powerful features in the SSIS. Executables such as packages, for loop, task host containers, etc., raise events at runtime. The most common event is a task that may fail. This raises a OnError event, the default event when the task fails. These events can be leveraged by handling the event in such a way that some other task can be accomplished or information about the failure gleaned. Thus, by using event handlers the package's functionality is extended.

Best practices to use an Event handler in the package but due to some reason, we want to disable all the event handlers from the package. In this case, we can do in two ways.       

• Delete all the event handlers from the package:

This is a tedious task and also not a good idea to delete all event handlers from the package. If in future we want to use this, we can’t use it.

• Use DisableEventHandlers property in the package:

           By setting the DisableEventHandlers is TRUE, we can disable all the event from the package.             By default, it is set to FALSE.

   

 Important Note:

When we are setting the property value TRUE in this case event handlers are disabled only not deleted. When we set it false we are able to use the existing event handlers.

Let’s see the example

In the package, I have created 4 Execute SQL task. Using this task I want to insert records in the table.

  

I also implement the error handling in the all task.

      

If any error occurs during the execution of the execute SQL task then errors are handled by the event handler and here I am using OnError event handler and inserting error log in the table.

I am having two table as below

create table EMP (EMPID int,EMPName nvarchar(50))

Store the employee information and

create table ErrorLog(Error_ID int IDENTITY, Error_MSG nvarchar(100));

In this table inserting error log.

Here I am inserting the wrong value

    

Forcing to fail the task.

For all four execute task the values are same. My aim to fail all task.

Now Out of 4 execute SQL task in 3 task I am selecting DisableEventHandlers property to true. For 3 task event handlers are not executing.

Before running of our package records in both table.

    

Before running the package see the property

     

Only for the EST_2 I set false.

   

Now executing my package.

Package executed and all task failed as we expected.

If we see the event hander pan only for the EST_2 event handlers executed.

See for others.

Not executed.

See the records in the able.

Now I am setting package level DisableEventHandlers property to TRUE.  See the result.

Executing the package.

Before running the package.

  

After package run.

All package get failed.

  

No, any event handler executed.

    

See the records in the table  

Now I am setting the property value to FALSE. Now all event handler will be executed.

  

See the records in the table.

    

Now executing the package.

Package executed. All event handlers are executed.

  

See the records in the database.

  

Now I am correcting the insert script and executing this package.

Before running the package records in the table.

    

Now running the package.

                    

If the package executed successfully OnError event will not occur.

See the records in the database table.

Hope this article is helpful to understand the DisableEventHandlers property in SSIS package.