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

3. Use a Conditional Split transform to compare the DQS knowledge
base date against the date the row was last processed. The expression
might look like this: [DateLastCleansed] <
@[User::DQS_KB_Date] . Rows matching this expression will be
directed to a DQS Cleansing transformation.
4. Handle the cleansed rows according to their status.
5. Use a Derived Column transform to set a new
DateLastCleansed value.
6. Update the destination table with any corrected values and the new
DateLastCleansed value.
Filtering Rows with the Lookup Transform
You can reduce the number of rows you need to cleanse by validating the data with a
faster data flow component, such as the Lookup transform. Using one or more Lookup
transforms, you can check if values exist in a reference table using quick, in-memory
comparisons. Rows that match existing values can be filtered out. Rows with values
that aren't found in the reference table can then be sent to Data Quality Services for
cleansing. Prefiltering rows this way means you won't be able to take advantage of the
standardized formatting that DQS provides, and this makes it difficult to do complex
validation that involves relationships between multiple fields. This approach works
best when you are working with a small number of unrelated fields that don't require
any special formatting as part of the cleansing process.
To use this pattern, your data flow will use the following logic:
1. Retrieve the data containing the fields to be cleansed using a source
component.
2. Set the component to Ignore failure when there are no match-
ing entries.
3. Add a Lookup transform for each field you are going to cleanse. Each
Lookup transform will use a SQL query that pulls in a unique set of
values for that field and a static Boolean (bit) value. This static value
will be used as a flag to determine whether the value was found.
Since you are ignoring lookup failures, the flag value will be NULL if
the lookup failed to find a match. Listing 5-2 shows what the query