Microsoft SQL Server 2005 Integration Services Step by Step

Paul Turley,

Joe Kasprzak,

Scott Cameron,

Satoshi Iizuka,

and Pablo Guzman

of Hitachi Consulting

PUBLISHED BY Microsoft Press A Division of Microsoft Corporation

One Microsoft Way, Redmond, Washington
98052-6399

Copyright © 2007 by Hitachi Consulting

9780735624054

All rights reserved. No part of the contents of this book may be reproduced or transmitted in any form or by any means without the written permission of the publisher.

Library of Congress Control Number: 2007928769

Printed and bound in the United States of America.

1 2 3 4 5 6 7 8 9 QWT 2 1 0 9 8 7

Distributed in Canada by H.B. Fenn and Company Ltd.

A CIP catalogue record for this book is available from the British Library.

Microsoft Press books are available through booksellers and distributors worldwide. For further information about international editions, contact your local Microsoft Corporation office or contact Microsoft Press International directly at fax [phone]. Visit our Web site at www.microsoft.com/mspress. Send comments to [e-mail].

Microsoft, Microsoft Press, Active Directory, ActiveX, Excel, IntelliSense, MSDN, SQL Server, Visual Basic, Visual C#, Visual Studio, Windows, and Windows Server are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. Other product and company names mentioned herein may be the trademarks of their respective owners.

The example companies, organizations, products, domain names, e-mail addresses, logos, people, places, and events depicted herein are fictitious. No association with any real company, organization, product, domain name, e-mail address, logo, person, place, or event is intended or should be inferred.

This book expresses the author's views and opinions. The information contained in this book is provided without any express, statutory, or implied warranties. Neither the authors, Microsoft Corporation, nor its resellers, or distributors will be held liable for any damages caused or alleged to be caused either directly or indirectly by this book.

Acquisitions Editor: Ken Jones

Developmental Editor: Devon Musgrave

Project Editor: Denise Bankaitis

Technical Reviewer: Bob Hogan

Body Part No. X13-76794

About the Authors

Paul Turley

Paul Turley is an architect for Hitachi Consulting and has been managing and developing business solutions for about 14 years for many companies, such as Hewlett-Packard, Walt Disney, and Microsoft. He manages the BI Training group for Hitachi's national External Education Services. Paul has taught application development and database design courses for a number of colleges and private training facilities. He has been a Microsoft Certified Solution Developer since 1996 and holds MCDBA, MCSD, MCT, MSF, and IT Project+ certifications. Paul has presented at various conferences and industry associations, including Microsoft SQL PASS in 2004, 2006, and 2007. He has authored and co-authored several books for Wrox Press/Wiley Publishing on Reporting Services, Analysis Services, TSQL, and Access. He is the primary author of Beginning Transact-SQL for SQL Server 2000 and 2005 and was the lead author for Professional SQL Server Reporting Services (2000 and 2005). He is also a contributing author for Beginning SQL Server 2005 Administration. He lives in Vancouver, Washington, with his wife, Sherri, four kids, one dog, two cats, and a bird.

Joe Kasprzak

Joseph Kasprzak is a manager of business intelligence (BI) solutions for Hitachi Consulting in Boston. He has over 14 years of comprehensive business, technical, and managerial experience, providing consulting services for clients in the financial services, retail, telecommunications, health care, hospitality, manufacturing, and government industries. He has helped architect, integrate, develop, and manage full life cycle implementations of strategic BI analytical systems. Joe is a leader in providing BI best practices, proven BI methodologies, BI technology assessments, retail marketing analytics, business performance score cards, labor analytics, KPI executive dashboards, corporate performance management and reporting, financial analytics, and database modeling and design. Joe resides seaside in Newburyport, Massachusetts, where he and his wife, Liz, enjoy sailing and local volunteering. Joe holds a Bachelor of Science degree in mathematics/chemistry from Assumption College in Worcester, Massachusetts, and has performed post-graduate studies in computer science at MIT in Cambridge, Massachusetts.

Scott Cameron

Scott Cameron, a Senior BI Architect at Hitachi Consulting, has been developing BI solutions for nine years and has over 20 years' data analysis experience. He has over five years' experience working with SQL Server 2005 BI components and has taught SQL Server BI courses in the United States and Europe. He has experience in the health care, software, retail, insurance, legal, vocational rehabilitation, travel, and mining industries. He has helped several large companies perform their initial implementation of Microsoft Analysis Services 2005 and helped several independent software vendors integrate Analysis Services into their products. He holds a Bachelor of Arts degree in economics and Asian studies from Brigham Young University; his Master of Arts degree in Economics is from the University of Washington. Scott lives in the Seattle, Washington, metropolitan area with his wife, Tarya, and beagles Hunter and Si.

Satoshi Iizuka

Satoshi Iizuka, an engineer with Hitachi Ltd. in Tokyo, has over nine years of database custom development experience and significant BI development experience. He is a member of the Windows COE initiative at Hitachi Ltd., which manages and educates best practices for Microsoft products (including Microsoft .NET technologies). He programs with almost all Microsoft programming languages and Java and is proficient with Microsoft major server products and multiple software development methodologies. He is a Microsoft Certified Systems Engineer (MCSE), Microsoft Certified Database Administrator (MCDBA), and Microsoft Certified System Developer (MCSE) for .NET. Satoshi lives in Tokyo, Japan, with his wife and favorite Nikon cameras.

Pablo Guzman

Pablo Guzman, a BI consultant at Hitachi Consulting, has been developing BI solutions for over seven years. Prior to joining Hitachi Consulting, Pablo worked as a BI consultant for around a year and then worked for three years in a software-development company where he built BI tools for the banking industry. After that, he worked three years at the largest bank in Quito, Ecuador, where he was the BI program manager. He has taught SQL Server BI courses and developed SQL 2005 BI training material. He has engaged with multiple clients in business groups that include data warehousing, IT, insurance, manufacturing, and banking. He has worked in numerous projects that involved performing complex analysis on large data sets. He received an engineering degree in computer and information systems from the National Polytechnic School of Ecuador. Pablo lives in the Seattle, Washington, metropolitan area, where he has been an active volunteer participant in some nonprofit organizations and plays guitar, bass, and drums in several bands in Seattle.

Supporting Author

Anne Bockman Hansen

Anne has ten years' project-based experience in technical writing and editing, instructional design, and project management. She is experienced in designing curriculum for a wide variety of content areas, including Microsoft Windows Server, Windows Small Business Server, Microsoft Exchange Server, SQL Server, and Microsoft Office. She is experienced in designing curriculum for a variety of learning levels, including Web developers, Microsoft Certified Solution Providers, Solution developers, technical implementers and decision makers, corporate developers, site administrators, senior support professionals, and technical consultants. Anne received a Master of Science degree in technical communication from the University of Washington College of Engineering in 1996 and a Bachelor of Science degree in cognitive psychology in 1980. Anne resides in the country in Fall City, Washington, with her husband, Barry.