Event Handlers in SSIS

Event Handlers in SSIS is one of the most useful and powerful features. At the run-time, executables such as Containers and Tasks will raise events.

For this SSIS Event Handlers example, before starting the task, OnPreExecute, or OnError event will raise when an error occurs, or the OnPostValidate event occurs after validating the task, etc.

A few examples where we use the SSIS Event Handlers are:

• Truncating or Cleaning tables before we start loading the data.
• Removing unwanted files after we exported them to another locations (or to SQL).
• Sending Emails when an error occurs.
• Retrieving system information etc.

In this article, we will show you, How to Configure Event Handlers in SSIS with an example. For this, we are going to use the Employee table present in the database

Without Event Handlers in SSIS Example

Drag the Execute SQL Task from SSIS Toolbar and drop it into the Control Flow region.

Next, Double click on the Execute Task will open the Editor to configure it. Let me select the Connection Type as OLE DB Connection, which is connecting to the database. Next, we use the Direct Input as the SQL statement, so click the … button to write the custom transact command.

Please write your custom statement here. As you can see from the below screenshot, we are writing an INSERT INTO Statement to insert four records into the table we created earlier.

INSERT INTO [dbo].[EmployeeDuplicates]
           ([FirstName]
           ,[LastName]
           ,[Education]
           ,[Occupation]
           ,[YearlyIncome]
           ,[Sales]
           ,[HireDate])
SELECT[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,[HireDate]
  FROM [dbo].[Employee]

Click OK to close the Execute SQL task editor.

Let us run the SSIS Event Handlers project and see whether we are successfully inserted records into Employee Duplicate records or not.

Event Handlers in SSIS Example

In order to configure these, Please navigate yourself to the Event Handlers tab as shown below.

Here, there are two sections in SSIS Event Handlers:

• Executable: You can use this drop-down list to select the Task, Containers on which you want to apply Event handling.
• Event Handler: This drop-down list has all the events. You can select the event that you want to handle. For example, sending an Email OnError event occurs.

From the below screenshot, you can see we are selecting the Execute SQL task as the Executable.

And selecting the OnPreExecute as the event that we want to handle.

Drag Execute Task from SSIS Toolbar, and drop it into the Event Handlers region. It means that before the package starts executing, this Execute SQL Task will execute.

Please write your custom statement here. As you can see from the below screenshot, we are writing a TRUNCATE Statement to delete the records present in the Employee Duplicate table.

-- example

TRUNCATE TABLE [dbo].[EmployeeDuplicates]

Click OK to close the Execute SQL task editor.

Let us run the SSIS Event Handlers package. This package will start with the TRUNCATE TABLE (Execute SQL Task in Event handler region), then it will start inserting data into the Employee Duplicate table in Control Flow Region.

Let us open the SQL Server Management Studio Query window to Preview the data. As you can that, the package inserted 14 records.

Let me add one more task to the Event Handler. This time, we are adding a Script Task to handle the OnPostExecution of Execute SQL Task.

Double click on the Script task will open the following editor to configure the Script task components. Please click on the Edit Script.. button to write the C# Script.

C# code we used in the below screenshot is:

// C# Script
MessageBox.Show("Hey!! This is Tutorial Gateway Custom Message from Post Execute");

As you can SSIS Event Handlers project is displaying the message after completing the Execute SQL task in the Control Flow region.