Error Rows in SSIS

Error rows have been briefly touched upon in several Topics, including The Data Flow Topic, Accessing Heterogeneous Data Topic and Understanding and Tuning the Data Flow Engine Topic. However, this Topic on SSIS error and event handling would be only partially complete without a further discussion on handling errors.

Error rows are handled in the Data Flow through the use of the Error Row Configuration properties. These properties tell the Data Flow Components what to do when a row fails an operation, such as data conversion, a missing lookup, or a truncation. The properties are found in sources, transformations, and destinations, depending on whether an error can occur or not.

The basic error properties window allows errors to be handled in one of three ways: failure of the Data Flow Task, ignoring the failure or redirecting the row. Furthermore, truncation errors can be handled separately from conversion errors. below screenshot shows the Error Output property page of an OLE DB Source.

Below screenshot also shows the dropdown selection of the ProductNumber column that defines how the SSIS Data Flow engine should handle an error row for the selected column. The following table clarifies the implications of the error handling section. The following table clarifies the implications of the error handling section.

To demonstrate how to use the error row handling, create a new package in SQL Server Data Tools (SSDT) with an OLE DB connection to the AdventureWorks database. In a new Data Flow Task, follow these steps:

1. Begin by running the following script (ProSSISScriptsCh18_ProSSISScripts.sql) against the AdventureWorks database to create the tables used later by this package:

2. Drag an OLE DB Source to the Data Flow and configure it to use the AdventureWorks connection for the OLE DB Connection Manager. Keep the default Data access mode option of “Table or view”; in the “Name of the table or the view” dropdown, choose the [Production].[Product] table from the list.
3. Drag a Data Conversion Component onto the Data Flow region and then connect the OLE DB Source to the Data Conversion.
4. Edit the Data Conversion Component and add a new row based on the input column Size. Name the output alias Size_Numeric and configure the new data type to be numeric [DT_NUMERIC], as shown in below screenshot.

The Data Conversion Component will create a new column in the Data Flow called Size_Numeric with a numeric data type, but the original Size column will remain, with the Unicode String data type with a length of 5.
5. Within the Data Conversion Component, click the Configure Error Output button in the bottom-left corner of the transformation, which will bring up the Configure Error Output window. Because only one column is defined in the Data Conversion Transformation, only one column is displayed for changing the error settings.
6. Change the Error value to Redirect Row for the Size_Numeric column, as shown in the figure below demonstrates

7. Return to the Data Flow by selecting OK in both the Configure Error Output and the Data Conversion Transformation Editor. 
The Data Conversion Component now has a yellow exclamation mark on it, indicating that an error row was configured to be redirected, but the red error path has not yet been used.

Frequently Asked SSIS Interview Questions & Answers

8. To fix this warning, you will set up an alternative path to direct those rows. Drag a Derived Column Transformation to the Data Flow and then connect the red error path from the Data Conversion to the new Derived Column Transformation. When you do this, the Configure Error Output window will automatically pop up for the Data Conversion Transformation. This is an alternate method to set the error handling for a failure. Click OK to return to the Data Flow.
9. Edit the Derived Column Transformation and add a new column named Size_Numeric. For the Expression field, type in the following code to add a 0 value to the records that failed the conversion in the prior transformation:(DT_NUMERIC,18,0)0
10. After saving the Derived Column Transformation, drag a Union All Transformation to the Data Flow and then connect both the blue data path output from the Data Conversion Transformation and the blue data path output from the Derived Column Transformation to the Union All Transformation.
11. Double-click the Union All Transformation to bring up its editor, and scroll down to the bottom of the column list. Multi-select both the ErrorCode and the ErrorColumn columns if they exist in the list, and then press Delete on your keyboard. Click OK to save the changes.
12. A useful tool in testing your package is the Multicast Transformation, which can be used as a placeholder destination as you are developing and testing your package. Before testing your package, add a Multicast Transformation to the Data Flow connected to the output of the Union All.
13. Run your package and observe the results. Your Data Flow execution will look similar to below screenshot.

Note that some of the rows are sent to the Derived Column Transformation but are brought back together with the main Data Flow rows through the Union All Transformation. At this point you have not added a destination, so the next step in this example is to land the data to a new destination table, where you can also handle errors.
14. Stop the package execution and replace the Multicast Transformation with an OLE DB Destination. In the editor, confirm that the AdventureWorks connection is listed in the OLE DB Connection Manager dropdown and change the data access mode to “Table or view.” Assign the data to load into the table UpdatedProducts that you created at the beginning of this exercise. On the Mappings property page, ensure that the default map of all the input columns from the Data Flow to the destination table columns based on name and data type looks accurate. Click OK to save the changes.
In the Data Flow designer, notice that the OLE DB Destination has a yellow exclamation point on it, and when you hover the mouse over it (or display the error window), it indicates that there may be a truncation error for the Name column going from a length of 50 to a length of 21. To finish this example, you will now redirect the error rows to a flat file.
15. Drag a Flat File Destination onto the Data Flow and connect the red error path output from the OLE DB Destination onto the Flat File Destination. When the Configure Error Output window pops up, change the Error handling dropdown to Redirect Row and click OK to save your changes.
16. Edit the Flat File Destination and click the New button next to the Flat File Connection Manager dropdown. When prompted, leave the Flat File Format selected on Delimited and click OK, which will bring up the Flat File Connection Manager Editor.
17. Type C:ProSSISFilesTruncated_Names.txt in the File name text box and then click OK to save the connection properties. You will be returned to the Flat File Destination Editor.
18. To finish, click the Mappings page in the Flat File Destination Editor, which will automatically map the columns from the error path to the flat file. Click OK to close the Destination Editor.
When you run your package, your results should look like what is shown in below screenshot.

The rows with the Name column truncation were sent to the flat file. You can check your results by opening the flat file in your C:ProSSISFiles folder, which will show that the full names were added to the flat file. As noted earlier, if you had set the OLE DB Destination error to Ignore Failure, the entire error row would be ignored, not just the column value.

List of Related Microsoft Certification Courses: