T-SQL: How to log erroneous entries during import

Question

Hi,

I do an import and convert columns to other datatypes. During the import I check, if I can convert the values stored in the columns into the target datatypes. If not I put a default value, e.g. I convert a string to int and when I cannot convert the string I insert -1.

At this moment it would be great if I could log the erroneous entries into a separate table, e.g. instead of a parsable string '1234' 'xze' arrives, so I put -1 into the target table and 'xze' into the log table. Is this possible with (T-)SQL?

Cheers,

Andreas

Answer 1

This is also very easy to do with SSIS data flow tasks. Any of the data conversion or lookup steps you might try have a "success" output and an "error" output. You simply direct all the error outputs to a "union" transform, and from there into a common error table. The result of all the successes go into your "success" table. You can even add extra details into the error table, to give you clear error messages.

The nice thing about this is that you still get high performance, as entire buffers move through the system. You'll eventually have buffers full of valid data being bulk written to the success table, and small buffers full of errors being written to the error table. When errors happen on a row, that row will simply be moved from one buffer of rows into another.

Answer 2

If you have a staging table, you can filter the good stuff into the final table and do a join back (NOT EXISTS) to find the rubbish for the log table