Errors got you down? Here's help!
If you've programmed with Data Transformation Services (DTS), you'll probably agree that error handling is one of the most confusing and challenging problems the DTS developer faces. In our July 1999 article, "The DTS Development Guide," we looked briefly at the built-in error-handling options the Package Designer offers. This month, we discuss in depth some programmatic opportunities for using the DTS Object Model to handle errors. We also examine DTS event handling.
The DTS Perspective on Errors
DTS doesn't consider an error to be a definitively right or wrong result, but rather the status of work accomplished. DTS leaves the interpretation of that status to the developer. Thus, developers can control the execution of a package, task, step, or transformation by monitoring the state or return value of the executed operation. For example, when transforming data via an ActiveX script, developers can control the processing by setting the return code in the script to any one of the values in the DTSTransformStatus enumeration list, which Table 1, page 46, shows. Table 2, page 46, lists other DTS status codes and result constants (their specific values are available in Books Online—BOL). Each of these lists of predefined constants lets developers determine whether, and in what manner, transformation processing will continue.
If you've previously developed only with the Package Designer, you haven't seen these values before because, aside from DTSTransformStatus, which is available to your ActiveX transformation scripts, the values are exposed only when you're writing directly to the DTS Object Model. Although the Package Designer lets you establish error-handling functionality in several places (we'll examine these shortly), it forces you to handle errors reactively. In other words, when the package encounters an error, it can only report or log the error. From then on, you have little control over the package execution. Depending on its type and source, an error generally causes the package execution to abruptly terminate. As a conscientious developer, you want to handle errors proactively—that is, you want to fully control how, when, and whether the error messages are communicated. You also want the flexibility to control the direction a package takes after it encounters an error.
Errors and the Package Designer
Let's review the error-handling capabilities the Package Designer affords within a package's logical control levels: package level, task level, and transformation level. At the highest level—the package—the designer offers little proactive control, as Screen 1, page 46, shows. The Designer lets developers designate an output file for logging errors or direct DTS to write the package's completion status to the Event Log. But both options are reactive. Developers can handle errors more proactively by enabling the Fail package on first error attribute, which tells DTS to halt execution after the first task failure. This specification might seem unnecessary, but even after a task fails, a package attempts to continue processing and uses precedence constraints to control its actions.
At the package's task level, precedence constraints offer the sole means of error control. In the Package Designer, this level provides the best proactive support. By using the Workflow Properties to manage workflow (right-click a constraint, and choose Properties, as Screen 2 shows), developers can designate which tasks happen and in what order, and can designate alternative tasks. For example, a common task in a data-warehousing scenario loads and scrubs raw data from various source systems before moving it to operational data tables. In this situation, as the arrows in Screen 3, page 48, illustrate, precedence constraints control the workflow from one task to the next (green arrows represent On Success constraints, and red arrows represent On Failure constraints).
The final level in the Package Designer, the transformation level, contains two types of error handling. The first type deals with the transformation in general. As with package-level errors, you use a simple dialog box to control the transformation settings, as you see in Screen 4. These settings are available from both the Data Driven Query task and the simple Data Pump task. Reactively, you can designate an output file and its format for logging transformation exceptions. You can proactively establish the maximum number of transformation exceptions before the transformation task aborts. As at the package level, you don't have much control at the transformation level.
The second type of transformation-level error handling offers you more control. An ActiveX transformation controls this type, which falls within the transformation. By using the various transformation and execution status codes, you can skip rows, reprocess them, and designate rows as exceptions without causing the task to fail. For example, suppose you need to process a large data file containing billing information. As part of the transformation process, you want to allow importing of only those rows that originate in Department 1234. Listing 1 contains a segment of control logic that you might use to perform this task. Notice how the logic uses the transformation status codes of DTSTransformStat_OK, DTSTransformStat_SkipRow, and DTSTransformStat_ExceptionRow to filter the data. By extending this logic, you can also perform any necessary cleansing operations, such as marking invalid amounts as exceptions.
The transformation level is as far as you can go in handling errors with the designer. Remember that the Package Designer simply acts as a wrapper, exposing some limited functionality of the underlying DTS Object Model. So, to reach the next level of control, you must peel away the wrapper and use the object model directly.
Errors and the Object Model
Developers who want to leverage the power of DTS quickly abandon the Package Designer and move on to designing custom packages by using the DTS Object Model natively. However, this approach poses a problem: How can you take advantage of the added functionality the object model exposes, while retaining the detailed error messages the package designer offers? After all, one nice feature of running packages through the Package Designer is the status dialog box, which details the tasks and their execution status. Most important, when something goes wrong, the Package Designer provides a dialog box containing a detailed error description. Because the Package Designer acts as a wrapper that exposes the underlying object model, the same errors the Package Designer reports must also be directly available in your Custom Packages, right? And they are! Before you examine how to obtain error information, you need to understand a few rules. The first rule is that package execution requires one or more steps. The second rule is that every task must be associated with at least one step. For simplicity, when you're developing packages graphically, the Package Designer hides both these rules. So, what do these rules have to do with errors? Put simply, packages and steps, not tasks, expose interfaces for examining error information. Now let's examine the two error-retrieval methods, beginning with the reactive approach the Step object offers.
GetExecutionErrorInfo
The first and simplest way to access a package's error information is through a step's GetExecutionErrorInfo method. We classify this method as reactive because it can only report on errors; it offers no direct ability to control subsequent execution of the package. In fact, this interface is available only after package execution is complete. For example, suppose your requirements are to develop a package and report any steps that failed. To demonstrate this process in action, we used Visual Basic (VB) to develop a package that attempts to run a stored procedure. (Subscribers can download the complete VB projects and all the code associated with this article at http://www.sqlmag.com.) The package contains an Execute SQL task called SQLTask and a step called ExecuteSQLStep. Listing 2 contains a sample of the code. After execution, the application queries the package's Steps collection and checks each Step object's ExecutionResult property to determine whether an error occurred. If so, the application calls the step's GetExecutionErrorInfo method to retrieve the error details. A message box then presents the results to the user. On first execution, the application calls the stored procedure sp_who and completes successfully. For the second run, change the stored procedure name from sp_who to sp_whois (which doesn't exist) and run the package again. Screen 5 shows the resulting error message. By quickly looking at the message, you can tell which step contains the error (ExecuteSQLStep) and which component is in error (Microsoft OLE DB Provider for SQL Server). And you can get a good description of the error: Could not find stored procedure 'sp_whois'. Now that you've seen the reactive side of capturing error information, let's examine the proactive approach.
Prev. page
[1]
2
next page 
