Home

SSIS Logging.

Logging and Exception Handling in the ETL Process

Like almost everything we do in IT, logging and exception handling are fundamental to a well designed solution.  In ETL we have two types of logging that we must do. First, we have operational data, like what is executing, start and end times, statuses, exceptions, and other events of interest to IT. Even more important in ETL is the data that is of interest to the business. Here we are talking about sources and destinations of data, record counts of source data, inserted rows, updated rows, etc. Equally important is the detection and logging of data that cannot be imported or exported and of course a user friendly error description. And, of course, that will lead us to notifications and reports which will be a separate article.
I work primarily with very large sets of data that must be received from or sent to some business partner either internal or external to the client business. I base my work around four fundamental principles:

• We must do everything in our power to avoid sending bad data to a business partner.
• We must assume that we will ocassionally receive bad data from a business partner. We must handle it, log it, and report it in a reliable and consistent manner.
• An ETL package must fully account for the data it extracts, transforms, and loads.
• An ETL process should be robust, catching and handling exceptions intelligently and degrading gracefully when necessary.

The consistent application of these principles will lead us to a discussion of building an ETL Framework consisting of a database and SSIS templates.  Although these principles apply to any ETL platform, I'm going to use Sql Server and SSIS as the basis of this discussion for the simple reason that I know the Microsoft stack better than some other platforms.
 

SSIS built in logging
As I'm sure you know, SSIS and all other major ETL platforms contain fairly robust logging mechanisms. In the case of SSIS, you can easily configure any of a number of events that will cause a record to be written to a table, a flat file, or the Windows Event Log.

You can find a detailed description of how to set up SSIS Logging in MSDN.

This is how the data looks when it has been written to the sysssislog table in the database of your choice.

Personally I find this data pretty hard to navigate, but I do recommend that you use it for the OnError and OnTaskFailed events. That ensures that in the worst case, where your SSIS package just crashes, there will be a record of what happened. Enabling other events, such as OnPostExecute or OnVariableValueChanged will sometimes be useful during the development phase, but ultimately they will eat up a lot of log space with little useful information, that will generally be unavailable to anyone who does not have at least read access to a production database. In other words, you will be logging data that you will never see again.

A more robust approach to logging
I recommend creating a database that is strictly for defining and logging ETL operations. What we'd like to end up with is a set of tables that we can easily query and even make some easy to read reports.

To start, we need to create tables that will define our SSIS packages, log package execution, log the execution of tasks within the package, and log any events of interest that tagke place during package or task execution. For now we are just going to look at the structure. We will talk about programming the SSIS package to work with this framework in a separate paper.

The SSIS package then interacts with the database in a very straightforward way.

• The Iniate Package task creates an entry in the PackageLog table
• The sequence container contains the main body of logic for the package. In every case there will be an Initiate Task sql task. This will create an entry in the TaskLog table. In most cases there will be a task name, a data source, and a data target that are known and will be entered into the table.
• The DataFlow task is of course the actual work that is being done by the package. As data is moved from one place to another and transforemed, variables for source record counts, insert counts, updates, etc. will be calculated. Equally important in this step is the exception handling that will take place within the data flow. Individual records that fail can be redirected and logged into the EventLog table.
• The third task in the sequence container, "Teminate Task", updates the TaskLog record created by Initiate Task with the final record counts, end time, and status.
• The Terminate Package task then updates the record in PackageLog with the end time and status of the package.

This concludes the initial introduction to SSIS Logging. In the following pages we will dig into the details of the database structure and programming and will we look in more detail at how the package interacts with the database. Finally, we will look at the logging and record level exception handling that take place within the DataFlow task.
Additional pages will be added that deal with specific scenarios in ETL.