SSIS: Custom Logging Using Event Handlers and SQL Server Job Scheduling

Generally we use the logging provided by SQL Server Integration Services (SSIS) in a database that contains metadata about SSIS packages. This allows us to easily have a corporate standard for what actions are logged and takes advantage of the work Microsoft did to create the logging system. SSIS contains some really useful logging procedures. However, the log data is relatively hard to read, but as with most things in SSIS, it is extensible. There are 2 methods of extending the logging capability of SSIS:

  • Build a custom log provider
  • Use event handlers

image

SSIS Package Logging

Using SSIS event handlers:

We use SSIS Component to pull the data into a table with the structure the way we need to see it. This has significantly reduced the time to research and fix errors.

To log events from SSIS Package, we need a Log table with following fields

EventID : Stores the value EventID.

EventType : Stores the name of event like OnPostExecute, OnError.

PackageName : Stores the name of SSIS package.

TaskName : Stores the name of Task that raises the event.

EventCode : Stores the ErrorCode value of event (events like OnError will provide a code).

EventDescription : Stores the description of the event (events like OnInformation will provide an

event description)

PackageDuration : Stores the values of package execution time

ContainerDuration : Stores the value of task execution time that raised the event

InsertCount : Stores the count value of rows that were inserted in table by the task

UpdateCount : Stores the count value of rows that were updated by the task

DeleteCount : Stores the count value of rows that were deleted by the task

Host : Stores the name of the machine on which the package was running on

All of the information that we are going to log will come straight out of SSIS. Most of the information is provided by system variables however in the case of InsertCount, UpdateCount & DeleteCount we are going to have to do a bit of work in the package to get these populated as you will see later.

Build the Custom Logging Functionality:

The event handlers provided with SSIS (The event handlers themselves are also extensible) are:

  • OnError
  • OnExecStatusChanged
  • OnInformation
  • OnPostExecute
  • OnPostValidate
  • OnPreExecute
  • OnPreValidate
  • OnProgress
  • OnQueryCancel
  • OnTaskFailed
  • OnVariableValueChanged
  • OnWarning

image

List of Events in SSIS

For the purposes of logging the most important events are OnPostExecute & OnError and of these 3 events the most important event of all is probably OnPostExecute because that enables us to put a message into log table after each task in our package has completed and send e-mail to the concerned user regarding the status of the package execution

The relevance of the container hierarchy

It is important to realise the role that the container hierarchy of a SSIS package plays here. Understanding containers is central to understanding SSIS and they are especially relevant for event handlers. Event handlers can be attached to any container in the package and that event handler will catch all events raised by that container and any child containers of that container. Hence, we can attach an event handler to the package (which is itself a container) and this one event handler will catch all events raised of that event type by every container in the package. This is powerful because it means we do not have to build event handlers for each task in the package.

Note: It is possible for a container to “opt out” of having its events captured by an event handler so we can simply switch them off using the sequence container’s DisableEventHandlers property.

Similarly, to capture only certain events of that sequence task, we can use System::Propagate variable.

OnError Event:

If the Pre-Processing step did not complete, this event will send notification to the users by e-mail along with reason.

On Event handlers tab, after selecting onError as an Event handler and selecting the package name as Executable, to log OnError event from SSIS package, we use

Execute SQL Task: Using the SQLStatementSource property of the Execute SQL Task by implementing the insertions to the log table, we can dynamically log the execution duration, host, task that raised the event etc.,

Send Mail Task: SendMail Task is used to send mail with the error log.

ScriptTask: As we are using 3 variables (insertcount, deletecount, updatecount) to track the number of rows processed by tasks. It is important that we reset these values to zero after every task otherwise the next task may report the same values. We do this using a script task.

image

OnPostExecute event:

To send notification to the users by email when the Membership Sync Process is complete, and that the error reports are available.

On Event handlers tab, after selecting onPostExecute as an Event handler and selecting the package name as Executable, to log OnpostExecute event from SSIS package, we use

Execute SQL Task: Using the SQLStatementSource property of the Execute SQL Task by implementing the insertions to the log table, we can dynamically log the execution duration, host, task that raised the event etc.,

Send Mail Task: Send Mail Task is used to send mail with the Error reports available.

ScriptTask: As we are using 3 variables (insertcount, deletecount, updatecount) to track the number of rows processed by tasks. It is important that we reset these values to zero after every task otherwise the next task may report the same values. We do this using a script task.

image

SQL Server Job Scheduling :

High level overview of SQL job agent is as shown in following diagram

image

Overview of implementation of SQL Job Agent in

According to requirement of real time SSIS package calling we need to implement job scheduling which cover Real time and Fixed job scheduling in

For we are going to implement dual job scheduling as follow

1. Fixed Job Scheduling

2. Real-time Job Scheduling

· Fixed Job Scheduling:

In this implementation we fixed the scheduling of job at one particular timestamp and job run at that particular instance

For e.g.: In if we fixed our Monthly run date as 15 then the SSIS Job will be run at the 15th day of every month

In short we can run job at specific time using fixed job scheduling.

· Real-time Job Scheduling

In this implementation we run the batch job on demand or any time when application need to run batch job. During real time we can set parameter of job agent and run the package as shown in following diagram:

image

During any instance of execution of Job package is going to log all the Details as explained in logging section and as shown in following diagram:

image

Here Parent package is going to call three child packages .Each of the child log one to allow operation to monitor one data set i.e. log all the action of each package in SQL Server

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

Up ↑

%d bloggers like this: