OnError Event
The DTS Package object lets applications respond through events to execution state changes. By default, DTS provides five events that developers can use to monitor the internal processing of a package. The OnFinish event executes at the completion of each task or step. The CustomTask uses the OnProgress event object to report progress. The OnQueryCancel event lets you terminate a running task, the OnStart event executes at the start of a task or step, and the OnError event provides information about an error. Developers can proactively control a package through these events. Let's look at how proactive error handling uses the OnError event.
Any time a DTS component or executing task raises an error, the OnError event fires. The event fires before a package returns from its Execute method. This distinction is important because it lets you choose which errors are terminal and which errors to handle. Besides reporting the error information, the OnError event also gives you a means to ignore errors and continue package execution. This option is helpful because by default, when a package encounters an error, it terminates execution.
To illustrate OnError error handling, we used a derivation of our VB application to serve as a model. This application implements the package as a class named cMyPackage, which subscribers can download at at the link to this article at http://www.sqlmag.com. This implementation is necessary for VB to handle the events.
To enable events within an application, VB uses the keyword WithEvents on an object variable declaration. Using WithEvents, however, limits how and where you can declare the variable. For example, you must declare within an object any variable that uses WithEvents (this variable might be a form or a class module). Also, you can't use WithEvents with the New keyword. The final gotcha in dealing with VB and events is the requirement that, to use events, an application must support all events the underlying object exposes. In this example, then, you need to supply the four remaining package events, although they won't contain any code.
To enable events within the class, use the WithEvents keyword and declare the package variable as follows:
Private WithEvents oPackage As DTS.Package
The remainder of the class contains the same functionality as the earlier example. The difference is that this time, the main program logic is encapsulated in the class' Execute method. You can see where we declared all the events at the end of the code.
Callout A in cMyPackage highlights the implementation of the OnError event. The event contains all the same error-reporting data as GetExecutionErrorInfo. In addition, by using the Cancel parameter, you can control whether package execution continues or stops. For this example, leave it set to the default value of TRUE, indicating that the package will terminate execution.
To use the new package, create the driver module in Listing 3. Using the package is as simple as declaring an object of cMyPackage type and firing its Execute method. On the first run, the stored procedure sp_who successfully executes. Because no errors occur, the OnError event doesn't fire. Next, change the stored procedure name to sp_whois. When the package executes this time, an error occurs, which in turn causes the OnError event to fire and display the message box in Screen 6.
So, How About Your Errors?
Using a combination of the OnError event and the GetExecutionErrorInfo method gives you many options for satisfying your error-reporting and flow-control needs. Also, by exploring other package events, you can quickly build a status console similar to the one the Package Designer provides. Although you might be able to deal with errors in other ways, understanding how DTS handles errors is the first step to building robust custom packages. As always, we're interested in hearing how you're handling errors, so email us about your experiences.