Expert SQL Server 2005 Integration Services

Brian Knight

Erik Veerman

Wiley Publishing, Inc.

Published by
Wiley Publishing, Inc.
10475 Crosspoint Boulevard
Indianapolis, IN 46256
www.wiley.com

© 2007 by Wiley Publishing, Inc., Indianapolis, Indiana

Published simultaneously in Canada

ISBN 9780470134115

10 9 8 7 6 5 4 3 2 1

Library of Congress Cataloging-in-Publication Data is available from the publisher.

No part of this publication may be reproduced, stored in a retrieval system or transmitted in any form or by any means, electronic, mechanical, photocopying, recording, scanning or otherwise, except as permitted under Sections 107 or 108 of the 1976 United States Copyright Act, without either the prior written permission of the Publisher, or authorization through payment of the appropriate per-copy fee to the Copyright Clearance Center, 222 Rosewood Drive, Danvers, MA 01923, [phone], fax [phone]. Requests to the Publisher for permission should be addressed to the Legal Department, Wiley Publishing, Inc., 10475 Crosspoint Blvd., Indianapolis, IN 46256, [phone], fax [phone], or online at http://www.wiley.com/go/permissions.

LIMIT OF LIABILITY/DISCLAIMER OF WARRANTY: THE PUBLISHER AND THE AUTHOR MAKE NO REPRESENTATIONS OR WARRANTIES WITH RESPECT TO THE ACCURACY OR COMPLETENESS OF THE CONTENTS OF THIS WORK AND SPECIFICALLY DISCLAIM ALL WARRANTIES, INCLUDING WITHOUT LIMITATION WARRANTIES OF FITNESS FOR A PARTICULAR PURPOSE. NO WARRANTY MAY BE CREATED OR EXTENDED BY SALES OR PROMOTIONAL MATERIALS. THE ADVICE AND STRATEGIES CONTAINED HEREIN MAY NOT BE SUITABLE FOR EVERY SITUATION. THIS WORK IS SOLD WITH THE UNDERSTANDING THAT THE PUBLISHER IS NOT ENGAGED IN RENDERING LEGAL, ACCOUNTING, OR OTHER PROFESSIONAL SERVICES. IF PROFESSIONAL ASSISTANCE IS REQUIRED, THE SERVICES OF A COMPETENT PROFESSIONAL PERSON SHOULD BE SOUGHT. NEITHER THE PUBLISHER NOR THE AUTHOR SHALL BE LIABLE FOR DAMAGES ARISING HEREFROM. THE FACT THAT AN ORGANIZATION OR WEBSITE IS REFERRED TO IN THIS WORK AS A CITATION AND/OR A POTENTIAL SOURCE OF FURTHER INFORMATION DOES NOT MEAN THAT THE AUTHOR OR THE PUBLISHER ENDORSES THE INFORMATION THE ORGANIZATION OR WEBSITE MAY PROVIDE OR RECOMMENDATIONS IT MAY MAKE. FURTHER, READERS SHOULD BE AWARE THAT INTERNET WEBSITES LISTED IN THIS WORK MAY HAVE CHANGED OR DISAPPEARED BETWEEN WHEN THIS WORK WAS WRITTEN AND WHEN IT IS READ.

For general information on our other products and services please contact our Customer Care Department within the United States at [phone], outside the United States at [phone], or fax [phone].

Trademarks: Wiley, the Wiley logo, Wrox, the Wrox logo, Programmer to Programmer, and related trade dress are trademarks or registered trademarks of John Wiley & Sons, Inc. and/or its affiliates, in the United States and other countries, and may not be used without written permission. Microsoft and SQL Server are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. All other trademarks are the property of their respective owners. Wiley Publishing, Inc., is not associated with any product or vendor mentioned in this book.

Wiley also publishes its books in a variety of electronic formats. Some content that appears in print may not be available in electronic books.

Dedication

To my best friend and the love of my life, Jennifer

-Brian Knight

To my lovely and supportive wife, Amy

-Erik Veerman

About the Authors

Brian Knight (SQL Server MVP, MCSE, MCDBA) of Green Cove Springs, Florida, is the co-founder of SQLServerCentral.com and JumpstartTV.com. He runs the local SQL Server users group in Jacksonville (JSSUG) and was on the Board of Directors of the Professional Association for SQL Server (PASS). He is a contributing columnist for SQL Server Standard and also maintains a regular column for the database web site, SQLServerCentral.com, as well as doing regular webcasts at JumpstartTV.com. He has co-authored and authored more than nine SQL Server books, including Admin911: SQL Server 2000 (McGraw-Hill Companies, 2001), Professional SQL Server 2000 DTS (Wiley Publishing, 2000), Professional SQL Server 2005 Administration (Wiley Publishing, 2006), and Professional SQL Server 2005 Integration Services (Wiley Publishing, 2006). He has spoken at conferences such as PASS, SQL Connections, and TechEd, as well as at many Code Camps. His blog can be found at www.whiteknighttechnology.com. He is an independent consultant at Pragmatic Works and spends most of his time trying to think about how to use the word onomatopoeia in everyday sentences.

Erik Veerman (SQL Server MVP, MCT, MCDBA) is a Mentor for Solid Quality Learning focusing on training, mentoring, and architecting solutions on the SQL Server Business Intelligence (BI) platform. His industry recognition includes Microsoft’s Worldwide BI Solution of the Year and SQL Server Magazine’s Innovator Cup winner. He has designed dozens of BI solutions across a broad business spectrum-telecommunications, marketing, retail, commercial real estate, finance, supply chain, and information technology. His experience with high-volume, multi-terabyte environments and SQL Server 64-bit has enabled clients to scale their Microsoft-based BI solutions for optimal potential. As an expert in OLAP design, ETL processing, and dimensional modeling, he is a presenter, author, and instructor. He led the ETL architecture and design for the first production implementation of SQL Server Integration Services (SSIS) and helped drive the ETL standards and best practices for SSIS on Microsoft’s SQL Server 2005 reference initiative (Project REAL). He also co-authored Professional SQL Server 2005 Integration Services (Wiley Publishing, 2006). As a resident of Atlanta, Georgia, Erik is a leader of the local Atlanta SQL Server User’s Group, a PASS and INETA user group chapter.

Contributing Author

Bayer White is Vice President of Information Technology, at Baywood Technologies (www.baywoodtech.com). He is responsible for architecture, development and implementation for wood procurement and security solutions. Bayer also participates in Software Design Reviews (SDR) for future versions of .NET. He has had the opportunity to contribute to the .NET community, by speaking at several Florida code camps, DevConnections, and for the past three years at Professional Association for SQL Server (PASS). Bayer is the co-founder of MSBICentral (www.msbicentral.com), a web community dedicated to Business Intelligence. Bayer blogs at www.whiteknighttechnology.com and he is also recognized as a community contributor by Microsoft’s Connected Framework Team for Windows Workflow Foundation. When Bayer is not writing code and speaking, he enjoys spending time with his wife, Robyn, and two-year-old daughter, Sarah.

Acknowledgments

First, thanks to my wonderful wife, who has always supported me in everything I’ve ever done and has been patient with the laptop never being put down. You’re the cornerstone of my day-to-day life, keeping me grounded. Thanks to my sons and my unborn child for allowing me to work these many late nights. An expression of huge appreciation goes to Grant Dickinson, who was the Technical Editor for this book. Grant went well above a Tech Editor, though, by providing valuable technical info and great constructive help. Thanks also to the makers of my favorite caffeine product, Mountain Dew, for keeping me awake late at night and early in the morning. Last, but most certainly not least, to Erik Veerman, who was an amazing co-author and took this book to the next level.

-Brian Knight

In many ways, this book represents the intersection of what I enjoy doing and the tool that makes it work. The content represents a culmination of years of ETL architecture, development, and mentoring for countless clients, whose interactions and challenges hopefully bring practicality and real-life exposure to these pages. Thank you to my clients for your (at times) painful data processing requirements!

It’s been a great four years working with SSIS, and there’s more to come. Thanks to Brian Knight for the book idea and for the tremendous community contribution. Also, much thanks to Grant Dickinson on the SSIS product team for his technical reviews of the chapters, which were thorough and insightful. Thanks to Dave Fackler (a partner in crime for years) and Paul Waters (with an unparalleled passion for BI), who both added value here through content reviews and talked through ideas and experiences.

Finally, and most importantly, thank you to my wife, Amy-there’s literally no one like her. And I cannot forget my precious children-Meg, Nate, and Kate. My faith, my family, my job, those are the things (in that order) that bring motivation to life and make an effort like this worth it. Soli Deo Gloria.

-Erik Veerman