T-SQL: how to log erroneous records during import
I am doing import and conversion of columns to other data types. During import, I check if I can convert the values stored in the columns to the target data types. If not, I put the default, eg. I am converting a string to int and when I cannot convert the string I insert -1.
At this point, it would be great if I could write the erroneous records to a separate table, for example. instead of the syntax string '1234' 'xze', so I put -1 in the target table and "xze" in the log table. Is this possible with (T-) SQL?
Greetings,
Andreas
a source to share
This is also very easy to do with SSIS data flow tasks. Any of the data transformation or search steps you can try will yield "success" and "error". You simply pipe all error outputs to the union transformation and from there to the general error table. The result of all successes is included in the "Success" table. You can even add additional data to the error table to give you clear error messages.
The nice thing is that you still get great performance as entire buffers are traversed through the system. You will end up with buffers filled with valid data written in bulk to the success table, and small buffers filled with errors written to the error table. When errors occur on a line, that line will simply move from one line buffer to another.
a source to share