Data Correction with Data Quality Services - SQL Server Integration Services Design Patterns - page 158

Option Description

New The value wasn't in the current domain and did not match any domain rules.

DQS is unsure whether or not it is valid. The value should be redirected and
manually inspected.

Auto sug-
gest

The value wasn't an exact match, but DQS has provided a suggestion. If you
include the Confidence field, you could automatically accept rows above a
certain confidence level and redirect others to a separate table for later review.

Note The column status values are localized; the actual string will change depending
on the language of your SQL Server installation. This might require you to add addi-
tional processing logic to your Conditional Split expressions if you expect your pack-
ages to run under different system locales. For more information about the status values,
see the “Data Cleansing” page in books online: http://msdn.microsoft.com/
en-us/library/gg524800.aspx .

The status values you handle and the downstream data flow logic you use will de-
pend on the goals of your data cleansing process. Typically, you will want to split your
rows into two paths. Correct , Corrected , and Auto suggest rows will go
down a path that will update your destination table with the cleansed data values
(found in the <column_name>_Output column). New and Invalid rows will
usually go into a separate table so that someone can examine them later on and either
correct the data (in the case of Invalid rows), or update the knowledge base (in the
case of New rows) so that these values can be handled automatically in the future. You
may wish to include a check against the confidence level
( <column_name>_Confidence ) of the Auto suggest rows to make sure it
meets a minimum threshold. Figure 5-12 shows an SSIS data flow with logic to process
rows from the DQS Cleansing transform.