Professional SQL Server™ 2005 Integration Services

1.7. Error Handling and Logging

In SSIS, the package events are exposed in the user interface, with each event having the possibility of its own event handler design surface. This design surface is the pane in Visual Studio where you can specify a series of tasks to be performed if a given event happens. There are a multitude of event handlers to help you develop packages that can self-fix problems. For example, the OnError error handler triggers an event whenever an error occurs anywhere in scope. The scope can be the entire package or an individual container. Event handlers are represented as a workflow, much like any other workflow in SSIS. An ideal use for event handlers would be to notify an operator if any component fails inside the package. You'll learn much more about event handlers in Chapter 13.

Handling errors in your data is easy now in SSIS 2005. In the data flow, you can specify in a transformation or connection what you wish to happen if an error exists in your data. You can select that the entire transformation fails and exits upon an error, or the bad rows can be redirected to a failed data flow branch. You can also choose to ignore any errors. An example of an error handler can be seen in Figure 1-8, where if an error occurs during the Derived Column transformation, it will be outputted to the data flow. You can then use that outputted information to write to an output log.

Figure 1.8. Figure 1-8

Once configured, you can specify that the bad records be written ...