Professional SQL Server 2005 Integration Services

Brian Knight,

Allan Mitchell,

Darren Green,

Douglas Hinson,

Kathi Kellenberger,

Andy Leonard,

Erik Veerman,

Jason Gerard,

Haidong Ji,

Mike Murphy

Published by Wiley Publishing, Inc.

10475 Crosspoint Boulevard Indianapolis, IN 46256.
www.wiley.com

Copyright 2006 by Wiley Publishing, Inc., Indianapolis, Indiana

Published simultaneously in Canada

ISBN-13: 978-0-7645-8435-0

ISBN-10:

0-7645-8435-9

Manufactured in the United States of America

10 9 8 7 6 5 4 3 2 1

1B/QZ/QR/QW/IN

Library of Congress Cataloging-in-Publication Data:

 Professional SQL Server 2005 integration services / Brian Knight … [ et al.]     p. cm.  Includes index. 

ISBN-13: 978-0-7645-8435-0 (paper/website)

ISBN-10: 0-7645-8435-9 (paper/website)

1. SQL server. 2. Database management. I. Knight, Brian, 1976-

QA76.9.D3P767 2005

005.75'85 — dc22

2005026347

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 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 WEB SITE 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 WEB SITE MAY PROVIDE OR RECOMMENDATIONS IT MAY MAKE. FURTHER, READERS SHOULD BE AWARE THAT INTERNET WEB SITES 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. 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.

About the Authors

Brian Knight, SQL Server MVP, MCSE, MCDBA, is the cofounder of SQLServerCentral.com and was recently on the Board of Directors for the Professional Association for SQL Server (PASS). He runs the local SQL Server users group in Jacksonville, Florida (JSSUG). Brian is a contributing columnist for SQL Server Standard and also maintains a weekly column for the database Web site SQLServerCentral.com. He is the author of Admin911: SQL Server (Osborne/McGraw-Hill Publishing) and coauthor of Professional SQL Server DTS and Professional SQL Server 2005 SSIS (Wiley Publishing). Brian has spoken at such conferences as PASS, SQL Connections, and TechEd. His blog can be found at www.whiteknighttechnology.com.

Allan Mitchell is joint owner of a UK-based consultancy, Konesans, specializing in ETL implementation and design. He is currently working on a project for one of the UK's leading investment banks doing country credit risk profiling as well as designing custom SSIS components for clients.

Darren Green is the joint owner of Konesans, a UK-based consultancy specializing in SQL Server, and of course DTS and SSIS solutions. Having managed a variety of database systems from version 6.5 onwards, he has extensive experience in many aspects of SQL Server. He also manages the resource sites SQLDTS.com and SQLIS.com, as well as being a Microsoft MVP.

Douglas Hinson, MCP splits his time between database and software development as a Senior Architect for Hinson & Associates Consulting in Jacksonville, Florida. Douglas specializes in conceptualizing and building insurance back-end solutions for payroll deduction, billing, payment, and claims processing operations in a multitude of development environments. He also has experience developing logistics and postal service applications.

Kathi Kellenberger is a database administrator at Bryan Cave LLP, an international law firm headquartered in St. Louis, Missouri. She fell in love with computers the first time she used a Radio Shack TRS-80, many years ago while in college. Too late to change majors, she spent 16 years in a health care field before switching careers. She lives in Edwardsville, Illinois, with her husband, Dennis, college-age son, Andy, and many pets. Her grown-up daughter, Denise, lives nearby. When she's not working or writing articles for SQLServerCentral.com, you'll find her spending time with her wonderful sisters, hiking, cycling, or singing at the local karaoke bar.

Andy Leonard is a SQL Server DBA, MCSD, and engineer who lives in Jacksonville, Florida. Andy manages a SQL Server DBA Team. He has a passion for developing enterprise solutions of all types and a fondness for business intelligence solutions in industrial enterprises. Learn more at www.andyleonard.net and reach Andy at [e-mail].

Erik Veerman is a mentor with Solid Quality Learning and is based out of Atlanta, Georgia. Erik has been developing Microsoft-based Business Intelligence and ETL-focused solutions since the first release of DTS and OLAP Server in SQL Server 7.0, working with a wide range of customers and industries. His industry recognition includes Microsoft's Worldwide BI Solution of the Year and SQL Server Magazine's Innovator Cup winner. Erik led the ETL architecture and design for the first production implementation of Integration Services and participated in developing ETL standards and best practices for Integration Services through Microsoft's SQL Server 2005 reference initiative, Project REAL.

Jason Gerard is President of Object Future Consulting, Inc., a software development and mentoring company located in Jacksonville, Florida (www.objectfuture.com). Jason is an expert with .NET and J2EE technologies and has developed enterprise applications for the health care, financial, and insurance industries. When not developing enterprise solutions, Jason spends as much time as possible with his wife Sandy, son Jakob, and Tracker, his extremely lazy beagle.

Haidong Ji (), MCSD and MCDBA, is a Senior Database Administrator in Chicago, Illinois. He manages enterprise SQL Server systems, along with some Oracle and MySQL systems on Unix and Linux. He has worked extensively with DTS 2000. He was a developer prior to his current role, focusing on Visual Basic, COM and COM+, and SQL Server. He is a regular columnist for SQLServerCentral.com, a popular and well-known portal for SQL Server.

Mike Murphy is a .NET developer, MCSD, and in a former life an automated control systems engineer currently living in Jacksonville, Florida. Mike enjoys keeping pace with the latest advances in computer technology, meeting with colleagues at Jacksonville Developer User Group meetings (www.jaxdug.com) and, when time allows, flying R/C Helicopters. To contact Mike, e-mail him at [e-mail] or visit www.murphysgeekdom.com.

Credits

Exceutive Editor
Bob Elliott

Development Editor
Brian MacDonald

Technical Editors
Slobodan M. Bojanic
James K. Howey
Ted Lee
Runying Mao
Ashwani Nanda
Ashvini Sharma

Production Editor
William A. Barton

Copy Editor
Publication Services

Editorial Manager
Mary Beth Wakefield

Production Manager
Tim Tate

Vice President and Executive Group Publisher
Richard Swadley

Vice President and Publisher
Joseph B. Wikert

Project Coordinator
Ryan Steffen

Graphics and Production Specialists
Denny Hager
Joyce Haughey
Jennifer Heleine
Barbara Moore
Alicia B. South

Quality Control Technicians
John Greenough
Brian H. Walls

Media Development Specialists
Angela Denny
Kit Malone
Travis Silvers

Proofreading and Indexing
TECHBOOKS Production Services

To my eternally patient wife, Jennifer

Acknowledgments

First and foremost, thanks to my wife for taking on the two small children for the months while I was writing this book. As always, nothing would be possible without my wife, Jennifer. I'm sorry that all I can dedicate to her is a technical book. Thanks to my two boys Colton and Liam for being so patient with their Dad. Thanks to all the folks at Microsoft (especially Ash) for their technical help while we were writing this. This book was turned good to great with the help of our excellent Development Editor Brian MacDonald. Once again, I must thank the Pepsi Cola Company for supplying me with enough caffeine to make it through long nights and early mornings. —Brian Knight

I would like to thank my wife, with whom all things are possible, and our son Ewan, who is the cutest baby ever, but I would say that, wouldn't I? I would also like to thank the SSIS team at Microsoft, in particular Donald Farmer, Ashvini Sharma, and Kirk Haselden, because let's face it, without them this book would not need to be written. —Allan Mitchell

I'd like to thank my wife Teri for being so patient and not spending too much time out shopping while I was holed up writing this. Thanks also go to the team in Redmond for answering all my questions and being so generous with their time. —Darren Green

First, I'd like to thank God for his continuous blessings. To my beautiful wife Misty, thank you for being so supportive and understanding during this project and always. You are a wonderful wife and mother whom I can always count on. To my son Kyle and daughter Mariah, you guys are my inspirations. I love you both. To my parents, thanks for instilling in me the values of persistence and hard work. Thanks, Jenny, for being my sister and my friend, and thanks to all my family for your love and support. Thanks to Brian MacDonald, Ashvini Sharma, and Allen Mitchell for doing the hard work of reading these long chapters and offering your advice and perspectives. A big thanks to the Team and Brian Knight for asking me to come along on this project in the first place and giving me this opportunity, which I have thoroughly enjoyed. —Douglas Hinson

I would like to thank my extended family, friends, and coworkers for their encouragement and sharing of my excitement about this project. Thanks to Doug Wilmsmeyer who advised me over 10 years ago to learn VB and SQL Server. Thanks to my brother, Bill Morgan, Jr., who taught me programming logic and gave me my first break programming ASP back in 1996. But most of all, thank you to Dennis, my husband, my partner, and love of my life. Because of all you do for me, I am able to live my dreams. —Kathi Kellenberger

I would first like to thank my wonderful wife. Christy signed on to this project when I did, and did as much to contribute to my part of this book. Christy, thank you for your unwavering support. Thanks to our son, Stevie, for giving up some playtime so Dad could write, and to Emma for just being cute. Thanks also to Manda and Penny for their support and prayers. Thanks to the team at work for their flexibility and inspiration, especially Mike Potts, Jason Gerard, Doug Hinson, Mike Murphy, and Ron Pizur. Finally, I would like to thank Brian Knight for his example, friendship, leadership, and the opportunity to write some of this book. —Andy Leonard

Thanks are in order to the Microsoft Integration Services development team for a few reasons. First, thank you for your vision and execution of a great product, one that has already made a big splash in the industry. Also, thanks to Donald Farmer and Ashvini Sharma (on the Microsoft development team) for your partnership since my first introduction to Integration Services in the summer of 2003; this includes putting up with my oftentimes nagging and ignorant questions, and talking through design scenarios and working with clients to make success stories. Much of those discussions and real-world lessons learned have been captured in the chapter I've contributed. A thanks needs to go to Mark Chaffin, a great contributor in the industry, for pulling me into this effort and for the many white-board design sessions we had putting this product into action. —Erik Veerman

Thanks go to my wife, Sandy, for putting up with my many late-night writing sessions. You were awesome during this whole experience. I would like to thank my son, Jakob, for making me laugh when I needed it. Many thanks to Doug Hinson for looking over my work and to Chad Crisostomo for critiquing my grammar. Thanks to Mike Potts for your support. Finally, thanks to Brian Knight for presenting me with this opportunity and to Andy Leonard for convincing me to do it. —Jason Gerard

I'd like to thank a lot of people who've helped me over the years. Thanks to my parents for their hard work and perseverance and for giving us an education in very difficult circumstances. Thanks to my brothers and their families for their help and care. Thanks to Brian Knight for introducing me to technical writing; I am very grateful for that. Thanks to Brian MacDonald, our editor, for his patience and excellent editing guidance. Finally, thanks to Maria and Benjamin, who are absolutely and positively the best thing that ever happened to my life. Maria, thank you for all you have done and for putting up with me. Benjamin, thank you for bringing so much joy and fulfillment into our lives. We are incredibly proud of you. —Haidong Ji

I would like to thank my parents, Barb and Jim, and my brother Tom for all their support throughout my life. Thanks to Sheri and Nichole for always believing in me. I would also like to thank Brian Knight for offering me this opportunity to expand my horizons into the world of writing, and Andy Leonard for keeping me motivated. And finally, thanks so much to all my friends and colleagues at work. —Mike Murphy