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
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
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.
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.
SQL Server Job Scheduling :
High level overview of SQL job agent is as shown in following diagram
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:
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:
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
(B)usiness (I)ntelligence Mentalist 