Event  Handlers  in  SSIS

It provide the ability to handle any type of event associated with Execution of its task  and Container.

The following list of Events is more significant and commonly used.


It is generated as the result of error condition. It falls into the category of the most frequently

Implemented types of Event Handlers .


On Warring:

Similar to on Error Event, It raised in response to problem.


On Task Failed:

It signals the failure of a task and typically follows on error event.


On post Validate:

Occurs as soon as the validation Process of the component is completed.


On Pre-Validate:

Marks the beginning of the component validation stage .The main Purpose of Validation is

Detection of Potential problems .

That might prevent execution from completing successfully.

At TekSlate, we offer resources that help you in learning various IT courses. We avail both written

material and demo video tutorials. To gain in-depth knowledge and be on par with practical

experience, then explore MSBI Training Certification.

Steps to Configure event Handler:

Open Business Intelligence Development Studio           --------------------->

Create a new Pkg and rename it as Event Handler. Dtsx           --------------------->

In control flow drag and drop Data Flow task and rename is as Data Flow Event Handler           --------------------->

In data flow drag and drop OLEDB source configure it(Provide connection manager and select

Any table from the list)           --------------------->

Drag and drop OLEDB destination and make a connection from OLEDB source to destination           --------------------->

Configure the destination (Provide destination connection manager and create a new table As Event handler)           --------------------->

Select Event Handler tab and set,

Execute table - Select Data flow Task Event Handler Option

Event Handler – Select on Pre-validate Option           --------------------->

Drag and Drop Execute SQL Task on to Event Handler Section and it will be executed where

Ever on Pre-Validate Event is raised on the Selected execute (DFT Events Handler)            --------------------->

Double click on Execute SQL Task to configure   it

Provide connection [Local Host. Adventure Work]             --------------------->

SQL Command – Truncate [Event Handler]           --------------------->

Click ok           --------------------->

Execute Package


Send Mail Task:

Send Mail Task is used to send an Email message to the recipients   via SMTP connection.


Send to Configure Send Mail Task

Drag And Drop Send Mail Task (dn control flow)           --------------------->

Double click on Send Mail Task to configure it            --------------------->

Provide the following information           --------------------->

SMTR connection - Select New connection           --------------------->

Provide SMTP Server Name             --------------------->

Click ok


From – Provide from email Address

To – Provide To email Address

Cc – Provide Carbon copy email Address (if any)

Bcc – Provide Blind Carbon copy email Address (if any)

Subject – Text Mail

Message Source Type – Direct input

Message Source – click Browse           --------------------->

Type body of Mail           --------------------->

Click ok

Priority – High

Attachments – Click Browse         --------------------->

Select   any file to be attached to the mail and click open           --------------------->

Click ok          --------------------->

Execute Package

Example for SMTP Connection – ASIHXCE. Vijay it.com

Check out the top MSBI Interview Questions now!

Migrate DTS 2000 package to 2005 dtsx package

Open Business Intelligence Development Studio 2005 and 2008         --------------------->

Select Project menu and select Migrate dts 2000 package option          --------------------->

In package migration wizard, click next set,         --------------------->

Source - Select Structured Storage File         --------------------->

File Name – click Browse          --------------------->

Select any package (.dts) and         --------------------->

Click open          --------------------->

Click Next         --------------------->

Source - File System

Click Next         --------------------->

Finally the migrated (.dtsx) package arrives in the specified Project under SSIS Package Selection          --------------------->

Execute DTS


Execute DTS 2000 package Task:

This task is used to execute a package designed

On SQL Server 2000 Environment.

Steps to Configure Execute DTS 2000 package task

In Control flow, Drag and Drop execute DTS 2000 package task      --------------------->

Double click on it and set,

Storage Location – Select Structured Storage file

File - click Browse      --------------------->

Select any package (.dts) and click open

Package Name – Specify the package name, which you would lick to

Execute it on SQL Server 2005/2008 environment      --------------------->

Click ok      --------------------->

Execute package

For an Indepth knowledge on MSBI click on: