Brian Knight - Professional Microsoft SQL Server 2012 Integration Services

CONTENTS

Professional Microsoft SQL Server 2012 Integration Services

Published by

John Wiley & Sons, Inc.

10475 Crosspoint Boulevard

Indianapolis, IN 46256

www.wiley.com

Copyright 2012 by John Wiley & Sons, Inc., Indianapolis, Indiana

Published simultaneously in Canada

ISBN: 978-1-118-10112-4

ISBN: 978-1-118-22380-2 (ebk)

ISBN: 978-1-118-23709-0 (ebk)

ISBN: 978-1-118-26211-5 (ebk)

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, (978) 750-8400, fax (978) 646-8600. Requests to the Publisher for permission should be addressed to the Permissions Department, John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030, (201) 748-6011, fax (201) 748-6008, 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 (877) 762-2974, outside the United States at (317) 572-3993 or fax (317) 572-4002.

Wiley publishes in a variety of print and electronic formats and by print-on-demand. Some material included with standard print versions of this book may not be included in e-books or in print-on-demand. If this book refers to media such as a CD or DVD that is not included in the version you purchased, you may download this material at http://booksupport.wiley.com . For more information about Wiley products, visit www.wiley.com .

Library of Congress Control Number: 2011945018

Trademarks: Wiley, the Wiley logo, Wrox, the Wrox logo, and Wrox Programmer to Programmer 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 registered trademarks of Microsoft Corporation. All other trademarks are the property of their respective owners. John Wiley & Sons, Inc. is not associated with any product or vendor mentioned in this book.

To my great team and work family at Pragmatic Works

Brian Knight

To my parents, Paul and Darian Veerman

Erik Veerman

For the SQL Community: without you, this would not be possible.

Jessica M. Moss

To my wife Jessy, she makes me a better person every day.

Mike Davis

To the three girls in my life that mean more than anything to me, Tammy, Calista, and Callie.

Chris Rock

ABOUT THE AUTHORS

BRIAN KNIGHT, SQL Server MVP, MCITP, MCSE, MCDBA, is the owner and founder of Pragmatic Works. He is also the cofounder of BIDN.com , SQLServerCentral.com , and SQLShare.com . He runs the local SQL Server users group in Jacksonville (JSSUG). Brian is a contributing columnist at several technical magazines. He is the author of a dozen SQL Server books. Brian has spoken at conferences like PASS, SQL Connections, and TechEd, SQL Saturdays, Code Camps, and many pyramid scheme motivational sessions. His blog can be found at http://www.bidn.com . Brian lives in Jacksonville, Florida, where he enjoys his kids and running marathons.

ERIK VEERMAN is a mentor with SolidQ focusing on training, mentoring, and architecting solutions on the SQL Server BI platform. Erik has designed dozens of BI solutions across a broad business spectrum telecommunications, marketing, retail, commercial real estate, finance, supply chain, and information technology. His industry recognition includes Microsofts Worldwide BI Solution of the Year and SQL Server Magazine s Innovator Cup winner. As an expert in OLAP design, ETL processing, and dimensional modeling, Erik is a presenter, author, and instructor. He has helped drive the industry ETL standards and best practices for SSIS through his Wrox books on SSIS and is the lead author of Microsoft Press SQL Server BI training kits. Erik lives in Atlanta, Georgia, with his wife and four energetic children.

JESSICA M. MOSS is a well-known practitioner, author, and speaker of Microsoft SQL Server business intelligence. She has created numerous data warehouse and business intelligence solutions for companies in different industries and has delivered training courses on Integration Services, Reporting Services, and Analysis Services. While working for a major clothing retailer, Jessica participated in the SQL Server 2005 TAP program where she developed best implementation practices for Integration Services. Jessica has authored technical content for multiple magazines, websites, and books, including the Wrox book Microsoft SQL Server 2008 Integration Services: Problem-Design-Solution , and has spoken internationally at conferences such as the PASS Community Summit, SharePoint Connections, and the SQLTeach International Conference. As a strong proponent of developing user-to-user community relations, Jessica actively participates in local user groups and code camps in central Virginia. In addition, Jessica volunteers her time to help educate people through the PASS organization.

MIKE DAVIS, MCTS, MCITP, is the Managing Project Lead at Pragmatic Works. This book is his third on the subject of business intelligence and specifically Integration Services. He has worked with SQL Server for almost a decade and has led many successful business intelligence projects with his clients. Mike is an experienced speaker and has presented at many events such as SQL Server user groups, code camps, SQL Saturday events, and the PASS Summit. Mike is an active member at his local user group (JSSUG) in Jacksonville, Florida. In his spare time he likes to play darts and guitar. You can also find him on twitter @MikeDavisSQL and his blog on MikeDavisSQL.com and BIDN.com .