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

Once configured, you can specify that the bad records be written to another connection, as shown in Figure 1-9. The On Failure precedence constraint can be seen as a red line that connects the Derived Column 1 task to the SQL Server Destination. The green arrows are the On Success precedence constraints. You can see the On Success constraint between the OLE DB Source and the Derived Column transform.

Figure 1-9

Logging has also been improved in SSIS 2005. It is now at a much finer detail than in SQL Server 2000 DTS. There are more than a dozen events that can be logged for each task or package. You can enable partial logging for one task and enable much more detailed logging for billing tasks. Some of the events that can be monitored are OnError, OnPostValidate, OnProgress, and OnWarning, to name just a few. The logs can be written to nearly any connection: SQL Profiler, text files, SQL Server, the Windows Event log, or an XML file.