SQL Server Integration Services

Example 9: Error Handling

This example will use a lookup to demonstrate error handling. In Example 3: Lookup, you configured the lookup to ignore SalesPersonID NULL values to keep package execution from failing. In this example, we will instead redirect errors to the lookup transformation error output.

This example reads all records from the Sales.SalesOrderHeader table. If the SalesPersonID is not NULL, the data flow will look up the name of the sales person and write the record to the destination flat file. If the SalesPersonID is NULL, a record will be output to an error file. A good starting point for this example is Example 1: Data Flow.

1. Copy Example1.dtsx following the instructions at the beginning of Example 2: Aggregation, and rename it Example9.dtsx. Open the Example9 package and select the Data Flow designer.

2. Delete the Sales Order Destination—the new destination file will have two extra columns for the sales person first and last name, and as a result, will be re-created.

3. Delete the Flat File Connection Manager from the Connection Managers pane.

4. Add the lookup of the sales person name. Drag a Lookup transformation onto the designer to the right of the Sum Amounts aggregate item. Rename the Lookup transformation to Lookup Sales Person.

5. Click the Sales Order Source to select it. Drag the data flow arrow onto Lookup Sales Person.

6. Double-click the Lookup transformation to open the Lookup Transformation Editor dialog.

7. Select the Reference Table tab. Click the New... button next to ...