Tutorial Gateway

  • C
  • C#
  • Python
  • SQL
  • Java
  • JS
  • BI Tools
    • Informatica
    • Talend
    • Tableau
    • Power BI
    • SSIS
    • SSRS
    • SSAS
    • MDX
    • R Tutorial
    • Alteryx
    • QlikView
  • More
    • C Programs
    • C++ Programs
    • Go Programs
    • Python Programs
    • Java Programs
  • MySQL

Event Handlers in SSIS

Event Handlers in SSIS is one of the most useful, and powerful feature. At the run-time executables such as Containers, 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 OnPostValidate event occurs after validating the task etc.

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 other location (or to SQL).
  • Sending Email 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 SQL Tutorial database

Event Handlers in SSIS 1

Without Event Handlers in SSIS Example

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

Event Handlers in SSIS 2

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

Event Handlers in SSIS 3

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

-- SQL statement for SSIS Event Handlers example
USE [SQL Tutorial]
GO
INSERT INTO [dbo].[EmployeeDuplicates]
           ([FirstName]
           ,[LastName]
           ,[Education]
           ,[Occupation]
           ,[YearlyIncome]
           ,[Sales]
           ,[HireDate])
SELECT[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,[HireDate]
  FROM [dbo].[Employee]
Event Handlers in SSIS 4

Click OK to close the Execute SQL task editor

Event Handlers in SSIS 5

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

Event Handlers in SSIS 6

Event Handlers in SSIS Example

In order to configure event handlers in SSIS, Please navigate yourself to the Event Handlers tab as we shown below.

Event Handlers in SSIS 7

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 in SSIS.
  • Event Handler: This drop-down list has all the event. You can select the event that you want to handle. For example, sending an Email OnError event occurs.
Event Handlers in SSIS 8

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

Event Handlers in SSIS 9

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

Event Handlers in SSIS 10

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

Event Handlers in SSIS 11

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

-- SQL statement for SSIS Event Handlers example
USE [SQL Tutorial]
GO
TRUNCATE TABLE [dbo].[EmployeeDuplicates]
Event Handlers in SSIS 12

Click OK to close the Execute SQL task editor

Event Handlers in SSIS 13

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 (Execute SQL Task in Control Flow Region)

Event Handlers in SSIS 14

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

Event Handlers in SSIS 15

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

Event Handlers in SSIS 16

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

Event Handlers in SSIS 17

C# code we used in the below screenshot is:

// C# Script for Event Handlering in SSIS
MessageBox.Show("Hey!! This is Tutorial Gateway Custom Message from Post Execute");
Event Handlers in SSIS 18

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

Event Handlers in SSIS 19

Filed Under: SSIS

  • What is SSIS
  • Install SQL Server Data Tools
  • Create a SSIS Project
  • Create SSIS Package
  • SSIS Connection Manager
  • OLE DB Connection Manager
  • SSIS ADO Connection Manager
  • ADO.NET Connection Manager
  • SSIS Cache Connection Manager
  • SSIS Excel Connection Manager
  • SSIS File Connection Manager
  • SSIS FTP Connection Manager
  • SSIS SMO Connection Manager
  • Source Assistance in SSIS
  • Flat File Source in SSIS
  • OLE DB Source in SSIS
  • Excel Source in SSIS
  • ADO.NET Source in SSIS
  • SSIS FLAT FILE Destination
  • SSIS OLE DB Destination
  • SSIS ADO.NET Destination
  • SSIS Transformations
  • SSIS Audit Transformation
  • SSIS Aggregate Transformation
  • SSIS Aggregate Advanced Mode
  • SSIS Cache Transformation
  • SSIS Character Map
  • SSIS Conditional Split
  • SSIS Copy Column
  • SSIS Data Conversion
  • SSIS Derived Column
  • SSIS Export Column
  • SSIS Fuzzy Grouping
  • SSIS Fuzzy Lookup
  • SSIS Import Column
  • SSIS Lookup Introduction
  • Lookup – OLE DB Connection
  • SSIS Lookup in Full Cache Mode
  • SSIS Lookup – Case Sensitivity
  • SSIS Merge Transformation
  • SSIS Merge Join Transformation
  • SSIS Merge Join – Left Outer Join
  • SSIS Merge – Right Outer Join
  • SSIS Merge Join – Full Outer Join
  • SSIS Multicast Transformation
  • SSIS OLEDB Command
  • OLEDB Command – Delete Data
  • OLEDB Command-Update Data
  • SSIS Percentage Sampling
  • SSIS Pivot Transformation 2008
  • SSIS Pivot Transformation
  • SSIS Row Count Transformation
  • SSIS Row Sampling
  • Script Component as Source
  • Script Component as Destination
  • SSIS Script as Transformation
  • SSIS Sort Transformation
  • SSIS SCD Type 0
  • SSIS SCD Type 1
  • SSIS SCD Type 2
  • SSIS Term Lookup
  • SSIS Term Extraction Intro
  • Term Extraction – Extract Nouns
  • SSIS Extract Noun Phrases
  • Extract Nouns & Noun Phrases
  • Term Extraction – Exclusion Tab
  • SSIS Unpivot Transformation
  • SSIS Union All Transformation
  • SSIS For Loop Container
  • SSIS ForEach File Enumerator
  • SSIS ForEach SMO Enumerator
  • SSIS ForEach Variable
  • SSIS Foreach NodeList
  • Foreach ADO.NET Schema Rowset Enumerator
  • SSIS Bulk Insert Task
  • SSIS Data Profiling Task
  • Execute T-SQL Statement Task
  • SSIS Execute SQL Task Intro
  • SSIS Execute SQL Task Example
  • Execute SQL Task- Single Rowset
  • Execute SQL Task – Full Row Set
  • SSIS Execute Package Task
  • Execute Packages in SQL Server
  • Execute Packages in File System
  • SSIS Execute Package Project Reference
  • SSIS File System Task
  • SSIS File System Task- Copy Files
  • File System Task-Copy Directory
  • File System Task – Delete Files
  • File System Task – Delete Folder
  • File System Task -Move Directory
  • SSIS File System Task -Move File
  • File System – Move Multiple files
  • File System Task – Rename File
  • File System Task – Set Attributes
  • SSIS FTP TASK
  • SSIS Create Local Directory
  • SSIS Create Remote Directory
  • SSIS FTP Task Send Files
  • SSIS FTP – Send Multiple Files
  • SSIS FTP Task Delete Local Files
  • FTP TASK Delete Local Directory
  • FTP Task Delete Remote files
  • SSIS Delete Remote Directory
  • SSIS FTP Task Receive Files
  • SSIS FTP Receive Multiple Files
  • SSIS Script Task
  • Transfer SQL Server Objects Task
  • Transfer SQL Table Structures
  • Transfer SQL Tables with Data
  • Transfer SQL Stored Procedures
  • Transfer User Defined Functions
  • Transfer SQL Views in SSIS
  • SSIS Web Service Task
  • SSIS XML Task-Validate XML File
  • Transform XML File using XSLT
  • XML Task-XML files Differences
  • Create SSIS Catalog
  • Package Deployment using BIDS
  • Deploy Package Using SQL
  • Deploy using SQL Server Wizard
  • SSIS Breakpoints
  • SSIS Checkpoints
  • SSIS Error Handling
  • SSIS Event Handlers
  • SSIS Transactions
  • SSIS Logging
  • SSIS Parameters
  • SSIS Package Configuration
  • Configure using SQL Server
  • Config using Registry Entry
  • Conf with Environment Variable
  • SSIS XML Configuration File
  • XML Configuration File Part 2
  • SSIS Package Protection Level
  • SSIS Incremental Load
  • Incremental Load Example 2
  • SSIS Remove Double Quotes

Copyright © 2021· All Rights Reserved by Suresh.
About | Contact | Privacy Policy