Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

James Serra's Blog

James is currently a Senior Business Intelligence Architect/Developer and has over 20 years of IT experience. James started his career as a software developer, then became a DBA 12 years ago, and for the last five years he has been working extensively with Business Intelligence using the SQL Server BI stack (SSAS, SSRS, and SSIS). James has been at times a permanent employee, consultant, contractor, and owner of his own business. All these experiences along with continuous learning has helped James to develop many successful data warehouse and BI projects. James has earned the MCITP Business Developer 2008, MCITP Database Administrator 2008, and MCITP Database Developer 2008, and has a Bachelor of Science degree in Computer Engineering. His blog is at .

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:

Data Conversion in SSIS

Cleanse and Match Master Data by Using EIM – Microsoft


Leave a comment on the original post [, opens in a new window]

Loading comments...