Data cleansing in SSIS, DQS, and MDS
Data cleansing can be done via SSIS as well as Data Quality Services (DQS) and Master Data Services (MDS). The lines are a bit blurred when talking about data cleansing using SSIS, DQS and MDS. In what product should data be cleaned? To give examples: having to convert a Unicode string to a non-Unicode string can be done in SSIS using the data conversion transformation; converting the word “one” to the number “1” would use the derived column transformation (which has a sophisticated expression language) in SSIS. Cleaning state codes by comparing them to a knowledge base/reference dataset containing valid state codes can be done with the lookup transformation in SSIS; removing duplicates from a table (i.e. a customer that is entered twice with a different spelling) can be done in SSIS using the fuzzy lookup transformation. These SSIS transformations would need to be used with other SSIS data flow components to fully complete the data cleaning solution.
But all those tasks can be done much easier using DQS, which also has a lot more features available. DQS enables you to build a knowledge base and use it to perform a variety of critical data quality tasks, including correction, enrichment, standardization, and de-duplication of your data (see Data Quality Services Books Online). Or you can perform matching manually within the Master Data Services Add-in for Excel which leverages the matching functionality in Data Quality Services. And there is a DQS cleaning transformation that you can use in SSIS (see Overview of the DQS Cleansing Transform).
MDS has limited data cleansing via business rules which can apply default values and change values. The best approach is to use DQS to clean the data from the source and then copy that data into MDS.
More info:
Cleanse and Match Master Data by Using EIM – Microsoft
Comparing Data Quality Services (DQS) and Master Data Services (MDS)
I like this post, as it gives some good examples as to what can be accomplished with DQS and MDS.
And on a side note, I can only smile over the (which has a sophisticated expression language) – because that is the understatement of the year ;o)
Thanks for the info on DQS. I was a bit skeptical about using it, since I’d only heard people mention it, but not what it does or how it works