Event Handlers in SSIS

Event  Handlers  in  SSIS

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

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

On ERROR:

It is generated as a result of the 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 the 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 the Detection of Potential problems. That might prevent execution from completing successfully.

Inclined to build a profession as MSBI Developer? Then here is the blog post on, explore MSBI Training

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 the 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 the 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           --------------------->

Provider 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 of SMTP Connection – ASIHXCE. Vijay it.com

MSBI Interview Questions

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 the package migration wizard, click next to 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 a 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