Sql Ssis

[pic]

Data Quality Solutions

SQL Server Technical Article






Writers: Elizabeth Vitt, Intellimentum
Hitachi Consulting
Technical Reviewers: Donald Farmer, Microsoft Corporation
Stacia Misner, Hitachi Consulting

Published: July 2006
Applies To: SQL Server 2005

Summary: This white paper describes how application developers can incorporate data quality into theirMicrosoft SQL Server 2005 Integration Services solutions.

Copyright


The information contained in this document represents the current view of Microsoft Corporation on the issues discussed as of the date of publication. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracyof any information presented after the date of publication.
This White Paper is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS DOCUMENT.
Complying with all applicable copyright laws is the responsibility of the user. Without limiting the rights under copyright, no part of this document may be reproduced, stored inor introduced into a retrieval system, or transmitted in any form or by any means (electronic, mechanical, photocopying, recording, or otherwise), or for any purpose, without the express written permission of Microsoft Corporation.
Microsoft may have patents, patent applications, trademarks, copyrights, or other intellectual property rights covering subject matter in this document. Except asexpressly provided in any written license agreement from Microsoft, the furnishing of this document does not give you any license to these patents, trademarks, copyrights, or other intellectual property.
Unless otherwise noted, the example companies, organizations, products, domain names, e-mail addresses, logos, people, places and events depicted herein are fictitious, and no association with anyreal company, organization, product, domain name, email address, logo, person, place or event is intended or should be inferred.

© 2006 Microsoft Corporation. All rights reserved.

Microsoft, Visual C#, and Visual Studio are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries.

The names of actual companies and products mentionedherein may be the trademarks of their respective owners.


Table of Contents

Introduction 4
Data Quality Strategy 4
SSIS Data Integration Solutions 5
Profiling 6
SSIS profiling scenario 8
Cleansing 12
SSIS cleansing scenario 13
Auditing 17
SSIS auditing scenario 18
SSIS Data Quality Partners 21
Conclusion 21
About the Authors 22

Introduction

The quality of the datathat is used by a business is a measure of how well its organizational data practices satisfy business, technical, and regulatory standards. Organizations with high data quality use data as a valuable competitive asset to increase efficiency, enhance customer service, and drive profitability. Alternatively, organizations with poor data quality spend time working with conflicting reports and flawedbusiness plans, resulting in erroneous decisions that are made with outdated, inconsistent, and invalid data.
To avoid the consequences of poor data quality, many organizations implement source system controls to ensure that their data satisfies quality standards at its point of origin. When properly implemented, source quality controls can effectively prevent the proliferation of invalid data.However, source system quality controls alone cannot enforce data quality. They cannot, for example, ensure that data quality is maintained throughout the data life cycle, especially when multiple data sources with varying levels of cleanliness are combined in downstream data integration processes. To address this potential problem, downstream applications must also include steps to ensure that...