Basic Error Detection and Handling

Before you begin to use event handlers for custom logging, it's important for you to understand the basic methods that SSIS provides for detecting and handling errors. You can perform basic error detection by maintaining and detecting changes in metadata. You can also use validation to verify package properties and configuration during package design and during package execution. SSIS also provides failure precedence constraints that can be used to determine the tasks that execute when an error occurs. Data flow transformations can be configured to fail on error, re-route error-causing records, or to ignore errors. This section describes these basic methods of error detection and handling.

Understanding Metadata Lineage

In a data flow task, upstream and downstream transformations establish a metadata contract with each other. If a column is modified in an upstream transformation, the downstream transformations recognize that the column's metadata has changed and require the metadata contract to be refreshed. The downstream transformation can then determine whether it is still valid, given the change in the column. For example, if a column in a data source transformation is changed from a numeric data type to a character data type, all downstream transformations will require their metadata contracts to be refreshed. As the metadata contracts are refreshed, downstream transformations become aware that the column has a character data type. Any transformation that performs a numeric operation on the column will then raise an error in the Data Flow designer.

Understanding Validation

SSIS provides validation to ensure that a package will not execute if a task or transformation is configured incorrectly. Validation verifies that SSIS can connect to all of the data sources and destinations, that component property values are valid and within range, that all metadata is correct, and so on. By default, all package components are validated twice:

• Before package execution begins

• Before individual components are executed

A package might fail the validation that occurs before package execution begins, even though the package can execute successfully. For example, a data source might not be available until after a package begins executing. Consider a package in which the first task copies a file into a folder and then a second task extracts data from the file. The package will raise an error when it is validated before package execution because the file is not in the folder. In such instances, packages and components have a DelayValidation property that can be set to True. The component will not be validated until just before it is executed.

Understanding Precedence Constraints

A precedence constraint is used to link two tasks (or containers) in a package. If it is a success precedence constraint, the first task must execute successfully before the second task will run. If it is a failure precedence constraint, the second task will execute only if the first task fails. You can use failure precedence constraints to execute a task that handles the error that occurred in the preceding task or to send a notification that an error occurred.

Understanding Data Flow Transformations

Data flow transformations are the components in the data flow of a package that aggregate, merge, distribute, and modify data. They can also perform lookup operations and generate sample datasets.

You can configure data flow transformations to fail when an error occurs, send an error-causing record to the transformation's error output, or ignore errors and send all records to the transformation's output.

Configuring a Transformation to Fail When an Error Occurs

When a data flow transformation is configured to fail when an error occurs, it causes the data flow task to fail also. When this occurs, you can use a failure precedence constraint to execute a task, or you can use an event handler to handle the error. (Event handlers are discussed later in this chapter.)

Configuring a Transformation to Re-route Error-Causing Records

Configuring a transformation to send error-causing records to the transformation's error output allows you to split the data flow pipeline so that successfully transformed data follows one branch of the pipeline, and data that generates errors follows a different branch of the pipeline. The error-causing records can then be subject to additional transformations or be sent to a file or database table for review and reprocessing. For example, records that fail to find a match in a Lookup transformation can be directed into the error output and routed into another Lookup transformation that applies a different matching logic. Records that fail to find a match in the second Lookup transformation can be written to a text file for manual review.

Configuring a Data Flow Transformation to Ignore Errors

You can configure a transformation to ignore errors and send all records to the transformation's output if an error can safely be ignored. When a transformation is configured as such, you might also want to use a derived column transformation to perform inline error handling or use a conditional split transformation to apply more sophisticated logic to re-route the error-causing records.