C H A P T E R 11
Events and Error Handling
We are ready for any unforeseen event that may or may not happen.
—Dan Quayle, former vice president of the United States
One of your tasks in building enterprise ETL solutions is making it ready to deal with unforeseen events—the kind that may or may not happen, as Dan Quayle so eloquently points out. Some of this planning comes in the form of infrastructure planning (such as hardware sizing and network and storage capacity planning), but a robust ETL system requires that you also focus attention on building in error handling. Although much of your attention will be focused on issues you can anticipate, unforeseen events can cause the biggest heartburn. This chapter covers SSIS events and event handling, as well as error handling and recovery.
SSIS Events
If you’ve written .NET Windows Forms programs or JavaScript functions for dynamic web pages, you are probably already familiar with the concept of events. Events are fired in response to specified activities. In a GUI-driven .NET application, you might capture events such as mouse clicks and keystrokes. In an application such as SSIS, which is not GUI driven, the events don’t depend on external user interface interactions but are instead driven by data processing activities.
Events in SSIS come in two flavors: events with event handlers, and logging events, which simply log information but have no event handlers. Events with event handlers give you an opportunity to run your own customized subroutines in response to events. In SSIS, event handlers are simply containers that hold a control flow which fires in response to an event. You can add any tasks to this control flow, although the most commonly used tasks tend to be Script tasks, Execute SQL tasks, and Data Flow tasks.
SSIS incorporates events into every ETL package you design. By default, SSIS raises several types of standard events at the task, container, and package levels. The built-in SSIS events are listed in Table 11-1 with the level at which they are fired.
The events listed in Table 11-1 are available at the task, container, and package level—with the exception of the Diagnostic event, which is available at only the package level. These events bubble (aside from the Diagnostic logging event), or propagate upwards, from the lowest-level task, up through the containers that hold them, and ultimately to the package level.
Figure 11-1 shows an OnError event fired at the Data Flow task level. This event propagates up through a Foreach loop container, which in turn fires it up to the package level. The package fires the event again. Each level has its own event handler for the OnError event, although you don’t have to implement code for all (or any) event handlers.
Figure 11-1. OnError event propagation in an SSIS package
Logging Events
SSIS supports several task-specific logging events. These logging events, with the exception of two we’ll talk about in a moment, are designated for logging additional information at the task level. These logging events do not have event handlers and cannot be propagated up to the container and package levels.
The Data Flow task is arguably the most important task in SSIS. As we discussed in previous chapters, the data flow provides the functionality for retrieving, transforming, and outputting your data. Because of its prominence and the complexity of the data flows you can create, it comes as no surprise that the Data Flow task exposes several task-specific logging events to help you troubleshoot and optimize your data movement and manipulation. These logging events are listed in Table 11-2.
Data Preparation tasks include the File System task, FTP task, Web Service task, and Data Profiling task. These tasks support the task-specific logging events listed in Table 11-3.
The Workflow tasks group includes the Execute Package task, Execute Process task, Message Queue task, Send Mail task, WMI Data Reader task, and WMI Event Watcher task. These tasks support the logging events listed in Table 11-4.
The WMIEventWatcherEventOccurred and WMIEventWatcherEventTimedout events are the exceptions in this group. Unlike the other task-specific logging events in this category, you can create event handlers for these two events, and they will bubble up to the container and package levels.
TIP: The Execute Package task does not have any task-specific logging events. However, the child package forwards log details to the parent package, which may log those details.
The group of SQL Server tasks includes the Bulk Insert task, Execute SQL task, Transfer Database task, Transfer Error Messages task, Transfer Jobs task, Transfer Logins task, Transfer Master Stored Procedures task, and Transfer SQL Server Objects task. The task-specific logging events supported by these tasks are listed in Table 11-5.
The Scripting task group includes just one task, the Script task. This group provides the task-specific logging event shown in Table 11-6.
Log Providers
In SSIS terminology, a log provider is a component that persists details about your package execution. SSIS logs the events you choose to log providers that you select in your package. SSIS provides several stock log providers to cover the most commonly used types of logging. These log providers are selected by right-clicking on an empty area of the control flow within BIDS and selecting Logging to access the logging options shown in Figure 11-2.
Figure 11-2. Choosing a log provider in an SSIS package
The containers and tasks are listed in the tree view on the left side of the editor. The Providers and Logs tab lets you configure the appropriate log providers. The stock log providers supplied by SSIS are listed in Table 11-7. Of these, the SQL Server provider, Text File provider, and Windows Event Log provider are the most commonly used log providers.
After choosing a log provider, many of them require you to choose a connection in the Configuration column. For the SQL Server provider, you need to choose an OLE DB Connection Manager. The text file, XML file, and SQL Server Profiler providers require a file connection. The Windows Event Log provider doesn’t require a connection. In Figure 11-3, we’ve selected the SQL Server log provider with an OLE DB Connection Manager named LOG.
Figure 11-3. Configuring an SSIS log provider for SQL Server
In the Details tab of the editor, you can choose the events that you want to log, as shown in Figure 11-4. You can choose different events to log for the package and for individual containers and tasks, or you can choose to log the same events for everything in the package.
Figure 11-4. Selecting events to log in the Details tab
You can even choose which elements of each event are logged by clicking the Advanced button. Generally speaking, you probably won’t need to change the default (log all elements) unless you have an event that logs very large MessageText or DataBytes entries, for instance. Figure 11-5 shows the Advanced options of the Details tab.
Figure 11-5. Advanced options of the logging Details editor
Figure 11-6 shows a sample of the log entries saved in the dbo.sysssislog table by the SQL Server log provider. The information logged includes the name of the event being logged; the name of the package, container, or task that fired the event; the start and end times; and a descriptive message of the event.
Figure 11-6. Sample SSIS log entries
When you run SSIS packages in BIDS, the events fired by the package can be viewed on the Progress tab, shown in Figure 11-7. The Progress tab is updated in real-time as the package runs, which is useful for package testing and debugging purposes.
Figure 11-7. SSIS Progress tab in BIDS
CHOOSING EVENTS TO LOG
Script Events
SSIS lets you fire events from Script tasks and script components. Both options give you the opportunity to add context, task-specific error messaging, and additional debugging and performance information to your packages. We discuss firing events from script in this section.
In your .NET script code, you can (and generally should) use try-catch exception handling within your code. If you need to raise an SSIS error within your component, you’ll want to use the events covered in the following sections.
Script Task Events
You can fire logging events with the Dts.Log() method in Script tasks. These logging events are like the built-in logging events in that they are sent to your log provider but do not have associated event handlers. They’re captured in your logging details with the ScriptTaskLogEntry event and are useful for sending user-defined informational messages to your log.
The Dts.Events object also provides several Fire methods to fire associated events, which can in fact have event handlers associated with them. These methods are listed in Table 11-8.
Most of these methods fire predefined standard events such as OnError and OnProgress. The FireCustomEvent() method, on the other hand, fires a custom event. The downside is that the Script task doesn’t provide a mechanism to register custom events in the task itself, so you can’t create an event handler for it. You also can’t choose it from the list of events to log in your package’s logging details. The best you can do is turn on logging for all events for the Script task, which will cause your custom events to be logged also. If you decide to not log even one event, SSIS won’t log your custom events either.
The Script task also exposes a Dts.Events.FireBreakpointHit() method, but this event is for internal use by the SSIS and BIDS infrastructure and is not designed to be used directly from your code. To demonstrate Script task event firing, we put together the sample package shown in Figure 11-8.
Figure 11-8. Sample package to demonstrate Script task events
In this package, we have a Sequence container that contains a Script task. In the Script task, we use Dts.Log() to generate a logging event and Dts.Events.FireError() to fire an OnError event. The sample script is as follows:
public void Main()
{
Dts.TaskResult = (int)ScriptResults.Success;
Dts.Log("We're about to throw an error.", -1, new byte[0]);
Dts.Events.FireError(-1, "SCR - Raise an Event", "Raised a custom exception.", "", 0);
}
The Dts.Log() method takes three parameters: a string message to log, an integer data code value, and a byte array representing the binary data bytes to log. The Dts.Events.FireError() method takes five parameters: an integer error code, a string subcomponent name, a string error description, a help file name, and an integer help context value. When there is no help file or help context, just use an empty string for the help file name and 0 for the help context. When run, this package fails with an error, as shown in Figure 11-9.
Figure 11-9. Sample package errors out
The events raised by the sample package can be viewed in the log, as shown in Figure 11-10. Notice that User:ScriptTaskLogEntry contains the data logged by the call to Dts.Log(). Also notice that the OnError event was logged three separate times: once by the Script task, then by the Sequence container, and again by the package. This is due to the event bubbling we discussed previously in this chapter.
Figure 11-10. Reviewing the log after package failure
Script Component Events
Script components in the data flow also allow you to fire events. Within script components, you can log entries by using the Log() method. This is the script component equivalent of the Script task’s Dts.Log() method. Like the Dts.Event object, the ComponentMetaData object exposes a handful of Fire methods to fire events. These methods are listed in Table 11-9.
FIREERROR() AND MAXIMUMERRORCOUNT
In the example shown in Figure 11-11, we created a simple package with a Data Flow task in a Sequence container. We then added a script component source and script component destination to the data flow. The script component source logs two messages, one each in the PreExecute() and PostExecute() methods, and fires an OnError event in the CreateNewOutputRows() method, as shown here:
public override void PreExecute()
{
base.PreExecute();
this.Log("Logging the first sample message.", -1, new byte[0]);
}
public override void PostExecute()
{
base.PostExecute();
this.Log("Logging the second sample message.", -1, new byte[0]);
}
public override void CreateNewOutputRows()
{
bool b = false;
this.ComponentMetaData.FireError
(
-1,
this.ComponentMetaData.Name,
"Firing an OnError event.",
"",
0,
out b
);
}
Figure 11-11. Raising events in script components
A quick query of the SSIS log shows that the two log entries and the OnError event were all logged in the correct order. Figure 11-12 shows the results as logged to the dbo.sysssislog table.
Figure 11-12. Results of script component log entries
Event Handlers
Event handlers are containers for control flows that are executed in response to SSIS events. Generally speaking, the SSIS stock events whose names begin with On, such as OnError and OnWarning, can have event handlers associated with them. For our example, we extended the previous sample to include OnError event handlers.
We created an event handler for the Data Flow task by highlighting it on the design surface and selecting the Event Handlers tab in BIDS. Then we selected the OnError event from the Event Handler drop-down list. We were presented with the window shown in Figure 11-13.
Figure 11-13. Adding an event handler to a task
At this screen, we clicked the blue link. BIDS created a container for us to hold a control flow associated with the event. In our example, the control flow was created on the OnError event. We added a Script task to the event handler that simply displays the message Task Error Raised. The completed OnError event handler is shown in Figure 11-14.
Figure 11-14. Completed OnError event handler
We then repeated the process to create OnError event handlers for our Sequence container and the package itself. When the sample package was run, it raised an OnError event at the Data Flow task level, which then bubbled up to the Sequence container and the package level. As the event bubbled upward, the event handler at each level displayed a message box, in the order shown in Figure 11-15.
Figure 11-15. Demonstrating OnError event bubbling in a package
In this example, we used the .NET MessageBox.Show() method to demonstrate event handlers and event bubbling in SSIS. Your event handlers can be much more complex and might include considerable logic. It’s common, for instance, to use event handlers to implement customized auditing and logging processes or to send e-mails to alert administrators of error conditions.
CAUTION: he .NET MessageBox.Show() method is very handy when debugging and troubleshooting SSIS packages. Be careful to remove these method calls when you deploy a package to production, however. If your package tries to display a message box in an automated job, no one can click the OK button, and your package may stop responding.
Summary
This chapter presented SSIS events and error handling, including event bubbling and Script tasks and script component methods for firing events. You looked at the different types of events you can fire and the purpose behind each. In the next chapter, you will consider how to incorporate data profiling and data scrubbing into your ETL processes.