Logging Error Rows with SQL Server SSIS
Don't let an error message or two stop you from getting what you need. Here's a quick trick to keep things running.
- By Eric Johnson
- 04/12/2010
When building an SSIS package, you probably find that a lot of the time you don't want one bad row to blow up the whole ETL. Many of the data flow tasks include the option to redirect errors down a different path. These bad rows can then be logged and reviewed later to see what the issue was.
Let's look at an example. Fig. 1 shows a simple data flow that loads data from a flat file, called NewStores.txt, to a table in SQL Server called Stores_Staging. Fig. 2. shows the mapping of the text file columns to the table columns.
 |
| Figure 1. Simple data fow from NewStores.txt. |
 |
| Figure 2. How the two text files are mapped. (Click image to view larger version.) |
This is a pretty simple load, but we still might have errors. What if you are supplied with a text file that has a blank StoreName, a required field in the table, or a City that is more than 50 characters? This will cause an error and the whole ETL will fail. To avoid this, we can just add destination and redirect rows with errors to that destination. Now our data flow looks a little different (see Fig. 3).
 |
| Figure 3. Errors are sent to another table, rather than disrupting the whole process. (Click image to view larger version.) |
As you can see in Fig 4., the errors have been redirected. When you hook up the error output to the new destination, you will see the following dialog box. Make sure that you set the dropdown to Redirect Row under the Error column.
 |
| Figure 4. Configuring the error trap. (Click image to view larger version.) |
Next time you get an error writing to the destination, the record, along with a couple columns of error information will be written to the text file we configured behind the Errors destination. This allows the good rows to be loaded and the bad rows to be logged for later review. Also, keep in mind that you can log to any of the available destinations in SSIS. One extra note, if you do log to a flat file and there are no errors, SSIS will still create an empty log file at the path you specified.
If you don't like this behavior you can write a script later in your SSIS package to clean up these empty files. Also remember that these error paths are just that; additional paths in your SSIS package. After the row is redirected, you can append audit details or even fix errors and attempt to load again. The sky is the limit, but it all starts with adding the extra path for error rows.
About the Author
Eric Johnson, SQL Server MVP, is the owner of Consortio Services in Colorado Springs providing IT systems management and technology
consulting. He is also the President of the Colorado Springs SQL Server
User Group. He can be contacted at www.consortioservices.com.