How to check for data quality in SSIS?

Question

When converting data during the transfer,I move all rejected (i.e. failed) conversions into a reject table. However, I only get an entry for the FIRST error:

Example source data:

Name | Salary  | Zipcode
------------------------
Paul | 12000   | 90210
Ringo| 5000    | 12345
Peter| hundred | London
Tina | 12345   | London

For row 3 I only get an error that the 2nd column is wrong, the 3rd column is not listed, ergo my rejects table only has 2 entries.

Is there a way to get a listing for each error?

Currently I would simply take the rejects table and run a script that does what I want over it, thus getting a "proper" logfile, though I would prefer to have something within SSIS.

What would be the best way to do this?

score 0 · Answer 1 · answered Oct 16 '19 at 15:40

I would look into doing more explicit validation and conversion in an asynchronous Script Transformation.

You would have two outputs, one for "OK", and one for "Error".

When each row comes in, you can perform validation methods on both fields (e.g., from the look of your data you'd probably be doing something like Int32.TryParse (C#)), and for each invalid field you encounter, you could write that field into an "Error" output field (you could also write the whole row, as well as a message to say which field has the error, if you wanted to see it like that).

If the data in the row is OK, then just write the single converted version of the row into the "OK" output.

How to check for data quality in SSIS?

1 Answers1