Move Error data to another table

Question

How do we redirect error/failed data to another table in SQL Server, during data importing in SSIS 2008 ?

Answer 1

In a particular data flow component - in Configure Error Output, choose to redirect the row. You may need to add some derived columns after that, and then union all your errors from different parts of your package together if you have just one unified error output.

Answer 2

Cade's way will work for any errors.

If you have data that you know in advance you want to redirect (say states that are not in a list of official states or people with no address), then you can do a conditional split and redirect the rows that way. I prefer to check for known problem issues rather than just to rely on something failing insert to avoid sending things to my datbase that might actually go into the filed but which are data I don't want. For instance I got a file that had the phrase "Legistlative restriction" in the last name field - this clearly wasn't a person, so I redirected the rows. But the actual text would have fit in our lastname field and the record would have been inserted if I had just reliedon error output.