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

named on the Mapping tab. In addition to the columns that are prefixed with the name
of the original, a Record Status column is added to record the overall status of the
row. Details on how to handle the columns added by the DQS Cleansing transform are
covered later in this chapter.

Table 5-2 . Additional Columns Created by the DQS Cleansing Transform

Column Default Description

Yes The overall status of the record, based on the status of each

mapped column. The overall status is based on the following al-
gorithm:
If one or more columns is
Invalid , the record status is Invalid .
Auto suggest , the record status is Auto suggest .
Corrected , the record status is Corrected .
If all columns are Correct or New , then the record status will
be Correct . If all columns are New , then the record status
will be New .See Table 5-3 for possible Status column values.

Record
Status

Yes This column contains the original value passed to the transform.

_Source

Yes If the original value was modified during the cleansing process,

this column contains the corrected value. If the value was not
modified, this column contains the original value. When doing
bulk cleansing through SSIS, downstream components will typ-
ically make use of this column.

_Output

Yes The validation or cleansing status of the value.

See Table 5-3 for possible values of the Status column.

_Status

_Confidence No This column contains a score that is given to any correction or

suggestion. The score reflects to what extent the DQS server (or
the relevant data source) has confidence in the correction/sug-
gestion. Most ETL packages will want to include this fieldand
use a conditional split to redirect values that do not meet the
minimum confidence threshold so that they can be manually in-
spected.

_Reason No This column explains the reason for the column's cleansing

status. For example, if a column was Corrected , the reason
might be due to the DQS Cleansing algorithm, knowledge base
rules, or a change due to standardization.