Lesson 5: Adding Error Flow Redirection
To handle errors that may occur in the transformation process, Microsoft SQL Server 2005 Integration Services (SSIS) gives you the ability to decide on a per component and per column basis how to handle data that cannot be transformed. You can choose to ignore a failure in certain columns, redirect the entire failed row, or just fail the component. By default, all components in Integration Services are configured to fail when errors occur. Failing a component, in turn, causes the package to fail and all subsequent processing to stop.
Instead of letting failures stop package execution, it is good practice to configure and handle potential processing errors as they occur within the transformation. While you might choose to ignore failures to ensure your package runs successfully, it is often better to redirect the failed row to another processing path where the data and the error can be persisted, examined and reprocessed at a later time.
In this lesson, you will create a copy of the package that you developed in Lesson 4: Adding Logging. Working with this new package, you will create a corrupted version of one of the sample data files. The corrupted file will force a processing error to occur when you run the package.
To handle the error data, you will add and configure a Flat File destination that will write any rows that fail to locate a lookup value in the Lookup Currency Key transformation to a file.
Before the error data is written to the file, you will include a Script component that uses script to get error descriptions. You will then reconfigure the Lookup Currency Key transformation to redirect any data that could not be processed to the Script transformation.
Important
This tutorial requires the AdventureWorksDW sample database. For more information about how to install and deploy AdventureWorksDW, see Running Setup to Install AdventureWorks Sample Databases and Samples.
Tasks in Lesson
This lesson contains the following tasks:
- Copying the Lesson 4 Package
- Creating a Corrupted File
- Adding Error Flow Redirection
- Adding a Flat File Destination
- Testing the Lesson 5 Tutorial Package