First when I saw Jack's article I used it in my packages.
It was about 5 months ago.
But later I switched to a different solution.
Let's say I have a Data Conversion task "CNV" in my Data Flow.
In "CNV" Error Output I redirected rows to OLE DB Command
with a simple SQL:
UPDATE t1 SET DQ_CD = 'CNV' WHERE RECORD_ID = ?
That's it. Now if a record fails conversion
DQ_CD (Data Quality Code) gets updated to 'CNV' and this will tell me exactly what's wrong with the record.
Both Jack's and my solution have a drawback.
If your record has 45 columns you have to spend a lot of time investigating which column caused you the conversion problem.
I thought about having 3 "CNV" tasks.
One for numeric, one for characters and one for dates.
Then you can write:
CNV-Num: UPDATE t1 SET DQ_CD = 'CNV-Num' WHERE RECORD_ID = ?
CNV-Char: UPDATE t1 SET DQ_CD = 'CNV-Char' WHERE RECORD_ID = ?
CNV-Date: UPDATE t1 SET DQ_CD = 'CNV-Date' WHERE RECORD_ID = ?
It will probably save you some time while troubleshooting
but I'm still not using this approach.