SSIS Event Handling
Introduction
One thing that I found when moving with SSIS is a lot of developer no care about SSIS Event handling. As it is a very important portion of the SSIS. We can perform a lot of job by this. This may be due to Innocence. So I decide to write an article related to Event Handling of SSIS.
What is that?
Before we move forward, we have to know what the Event Handling is. Event handling the name is self explanatory. We would like to perform some job based on certain event. The job may be anything like cleaning a table after the ELT process is completed or sending an email in case of any failure etc.
Setup the Event Handling Process
Step-1 [ The Data flow of SSIS Package ]
We have a simple package that extract flat file and load the data into SQL server table.
Please note that : To test this process we have to introduce an error within this package. In the flat file source we are going to delete the fat file from where the package extracts the data and this action caused an error for this package.
Step-2 [ The Error Log file ]
Now using SSMS we are just create a table to log the error.
IF OBJECT_ID(N'Tbl_ErrorLog', N'U') IS NOT NULL
BEGIN
DROP TABLE Tbl_ErrorLog;
END
GO
CREATE TABLE Tbl_ErrorLog
(
[ID] [int] IDENTITY(1,1) NOT NULL,
[PackageID] [uniqueidentifier] NULL,
[Error] [nvarchar](MAX) NULL,
[Source] [nvarchar](100) NULL,
[PackageName] [nvarchar](100) NULL
);
GO
Step-3 [ The Event Handler Tab ]
The event handler tabs have Executable and event handler drop down.
Here in this package we choose the Data Flow Task as Executable and ON Error as Event Handler. Then we click the hyper link called "Click here to create an 'OnError' event handler for executable 'Data Flow Tasks' ". When we click the hyper link the actual event handler is added with data flow task. To delete this event handler just click the delete button.
Different type of Event Handler is listed bellow
- OnError: This event is raised by an executable when an error occurs.
- OnExecStatusChanged: This event is raised by an executable when its execution status changes.
- OnInformation: This event is raised during the validation and execution of an executable to report information. This event conveys information only, no errors or warnings.
- OnPostExecute: This event is raised by an executable immediately after it has finished running.
- OnPostValidate: This event is raised by an executable when its validation is finished.
- OnPreExecute: This event is raised by an executable immediately before it runs.
- OnPreValidate: This event is raised by an executable when its validation starts.
- OnProgress: This event is raised by an executable when measurable progress is made by the executable.
- OnQueryCancel: This event is raised by an executable to determine whether it should stop running.
- OnTaskFailed: This event is raised by a task when it fails.
- OnVariableValueChanged: This event is raised by an executable when the value of a variable changes. The event is raised by the executable on which the variable is defined.
- OnWarning: This event is raised by an executable when a warning occurs.
Here in this point if we look at the toolbars we can see all the tasks in the control flow item. We can design a small package over here (even handler tabs) to perform its own ETL process. What you think? Is not Event Handling is so powerful.
Now we drag an Execute SQL Task over the Event Handler Tab. In the SQL statement we use the following sql statement.
INSERT INTO Tbl_ErrorLog
([PackageID], [Error], [Source], [PackageName])
VALUES (?, ?, ?, ?)
Now we set the parameter mapping option of Execute SQL task Editor.
Parameters Mapping
Table Columns
|
Variable Name
|
Data Type
|
Parameter Name
|
PackageID
|
System::PackageID
|
GUID
|
0
|
Error
|
System::ErrorDescription
|
NVARCHAR
|
1
|
Source
|
System::SourceName
|
NVARCHAR
|
2
|
PackageName
|
System::PackageName
|
NVARCHAR
|
3
|
Step-4 [ Run the Package and see the Error Log ]
Hope you like it.
