Christian Cote, Matija Lah, Dejan Sarka SQL Server 2017 Integration Services Cookbook ETL techniques to load and transform data from various sources using SQL Server 2017 Integration Services (cid:52)(cid:50)(cid:45)(cid:2)(cid:52)(cid:70)(cid:83)(cid:87)(cid:70)(cid:83)(cid:2)(cid:20)(cid:18)(cid:19)(cid:25)(cid:2)(cid:42)(cid:79)(cid:85)(cid:70)(cid:72)(cid:83)(cid:66)(cid:85)(cid:74)(cid:80)(cid:79) (cid:52)(cid:70)(cid:83)(cid:87)(cid:74)(cid:68)(cid:70)(cid:84)(cid:2)(cid:36)(cid:80)(cid:80)(cid:76)(cid:67)(cid:80)(cid:80)(cid:76) (cid:38)(cid:53)(cid:45)(cid:2)(cid:85)(cid:70)(cid:68)(cid:73)(cid:79)(cid:74)(cid:82)(cid:86)(cid:70)(cid:84)(cid:2)(cid:85)(cid:80)(cid:2)(cid:77)(cid:80)(cid:66)(cid:69)(cid:2)(cid:66)(cid:79)(cid:69)(cid:2)(cid:85)(cid:83)(cid:66)(cid:79)(cid:84)(cid:71)(cid:80)(cid:83)(cid:78)(cid:2)(cid:69)(cid:66)(cid:85)(cid:66)(cid:2)(cid:71)(cid:83)(cid:80)(cid:78)(cid:2)(cid:87)(cid:66)(cid:83)(cid:74)(cid:80)(cid:86)(cid:84) (cid:84)(cid:80)(cid:86)(cid:83)(cid:68)(cid:70)(cid:84)(cid:2)(cid:86)(cid:84)(cid:74)(cid:79)(cid:72)(cid:2)(cid:52)(cid:50)(cid:45)(cid:2)(cid:52)(cid:70)(cid:83)(cid:87)(cid:70)(cid:83)(cid:2)(cid:20)(cid:18)(cid:19)(cid:25)(cid:2)(cid:42)(cid:79)(cid:85)(cid:70)(cid:72)(cid:83)(cid:66)(cid:85)(cid:74)(cid:80)(cid:79)(cid:2)(cid:52)(cid:70)(cid:83)(cid:87)(cid:74)(cid:68)(cid:70)(cid:84) Christian Cote Matija Lah Dejan Sarka BIRMINGHAM - MUMBAI SQL Server 2017 Integration Services Cookbook Copyright © 2017 Packt Publishing All rights reserved. No part of this book may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, without the prior written permission of the publisher, except in the case of brief quotations embedded in critical articles or reviews. Every effort has been made in the preparation of this book to ensure the accuracy of the information presented. However, the information contained in this book is sold without warranty, either express or implied. Neither the authors, nor Packt Publishing, and its dealers and distributors will be held liable for any damages caused or alleged to be caused directly or indirectly by this book. Packt Publishing has endeavored to provide trademark information about all of the companies and products mentioned in this book by the appropriate use of capitals. However, Packt Publishing cannot guarantee the accuracy of this information. First published: June 2017 Production reference: 1300617 (cid:49)(cid:86)(cid:67)(cid:77)(cid:74)(cid:84)(cid:73)(cid:70)(cid:69)(cid:2)(cid:67)(cid:90)(cid:2)(cid:49)(cid:66)(cid:68)(cid:76)(cid:85)(cid:2)(cid:49)(cid:86)(cid:67)(cid:77)(cid:74)(cid:84)(cid:73)(cid:74)(cid:79)(cid:72)(cid:2)(cid:45)(cid:85)(cid:69)(cid:16) (cid:45)(cid:74)(cid:87)(cid:70)(cid:83)(cid:90)(cid:2)(cid:49)(cid:77)(cid:66)(cid:68)(cid:70) (cid:21)(cid:23)(cid:2)(cid:45)(cid:74)(cid:87)(cid:70)(cid:83)(cid:90)(cid:2)(cid:52)(cid:85)(cid:83)(cid:70)(cid:70)(cid:85) (cid:35)(cid:74)(cid:83)(cid:78)(cid:74)(cid:79)(cid:72)(cid:73)(cid:66)(cid:78) (cid:35)(cid:21)(cid:2)(cid:20)(cid:49)(cid:35)(cid:14)(cid:2)(cid:54)(cid:44)(cid:16) ISBN 978-1-78646-182-7 (cid:88)(cid:88)(cid:88)(cid:16)(cid:81)(cid:66)(cid:68)(cid:76)(cid:85)(cid:81)(cid:86)(cid:67)(cid:16)(cid:68)(cid:80)(cid:78) Credits Authors Copy Editor Christian Cote Safis Editing Matija Lah Dejan Sarka Reviewers Project Coordinator Jasmin Azemovic Nidhi Joshi Marek Chmel Tomaz Kastrun Ruben Oliva Ramos Commissioning Editor Proofreader Amey Varangaonkar Safis Editing Acquisition Editor Indexer Vinay Agrekar Pratik Shirodkar Content Development Editor Graphics Cheryl Dsa Tania Dutta Technical Editor Production Coordinator Dinesh Pawar Deepika Naik About the Authors Christian Cote is a database professional from Montreal, Quebec, Canada. For the past 16 years, he's been involved in various data warehouse projects and business intelligence projects. He has contributed to business intelligence solutions in various domains like pharmaceutical, finance, insurance, and many more. He's been a Microsoft Most Valuable Professional since 2009 and leads the Montreal PASS chapter. Matija Lah has more than 15 years of experience working with Microsoft SQL Server, mostly from architecting data-centric solutions in the legal domain. His contributions to the SQL Server community have led to the Microsoft Most Valuable Professional award in 2007 (data platform). He spends most of his time on projects involving advanced information management, and natural language processing, but often finds time to speak at events related to Microsoft SQL Server where he loves to share his experience with the SQL Server platform. Dejan Sarka, MCT and SQL Server Most Valuable Professional, is an independent trainer and consultant who focuses on the development of database and business intelligence applications, located in Ljubljana, Slovenia. Besides his projects, he spends around half of his time on training and mentoring. He is the founder of the Slovenian SQL Server and .NET users group. Dejan is the main author and coauthor of many books and courses about databases and SQL Server. He is a frequent speaker at many worldwide events. About the Reviewers Jasmin Azemovic is a university professor, active in the areas of database systems, information security, data privacy, forensic analysis, and fraud detection. His PhD degree was in the field of modeling design and developing an environment for the preservation of privacy inside database systems. He is the author of many scientific-research papers and two books: Writing T-SQL Queries for Beginners Using Microsoft SQL Server 2012 and Securing SQL Server 2012. He is an active member of the professional IT world: Microsoft MVP (Data Platform—eight years so far) and a security consultant. He is an active speaker at many IT professional and community conferences. Marek Chmel is an IT consultant and trainer with more than 10 years of experience. He's a frequent speaker with a focus on Microsoft SQL Server, Azure ,and security topics. Marek writes for Microsoft's TechnetCZSK blog, and since 2012 he's an MVP: Data Platform. Marek is also recognized as a Microsoft Certified Trainer: Regional Lead for Czech Republic for a few years in a row, he holds many MCSE certifications, and on the top of that he's an ECCouncil Certified Ethical Hacker and holder of several eLearnSecurity certifications. Marek earned his MSc (Business and Informatics) degree from Nottingham Trent University. He started his career as a trainer for Microsoft Server courses. Later, he joined AT&T, as a sr. database administrator with a specialization in MSSQL Server, Data Platform, and Machine Learning. Tomaz Kastrun is an SQL Server developer and data analyst. He has more than 15 years of experiences in the field of business warehousing, development, ETL, database administration, and query tuning. He also has more than 15 years of experience in the fields of data analysis, data mining, statistical research, and machine learning. He is Microsoft SQL Server MVP for data platforms and has been working with a Microsoft SQL Server since version 2000. Tomaz is a blogger, the author of many articles, the coauthor of a statistical analysis book, speaker at community and Microsoft events, and an avid coffee drinker. Thanks to people who inspired me, the community, and the SQL family. Thank you, dear reader, for doing this. For endless inspiration, thank you Rubi. Ruben Oliva Ramos is a computer systems engineer with a master's degree in computer and electronic systems engineering, teleinformatics, and networking specialization from University of Salle Bajio in Leon, Guanajuato, Mexico. He has more than five years of experience in developing web applications to control and monitor devices connected with Arduino and Raspberry Pi using web frameworks and cloud services to build IoT applications. He is a mechatronics teacher at University of Salle Bajio and teaches students on the master's degree in Design and Engineering of Mechatronics Systems. He also works at Centro de Bachillerato Tecnologico Industrial 225 in Leon, Guanajuato Mexico, teaching subjects like: electronics, robotics and control, automation and microcontrollers at mechatronics technician career, consultant and developer projects in areas like: monitoring systems and datalogger data using technologies: Android, iOS, Windows Phone, HTML5, PHP, CSS, Ajax, JavaScript, Angular, ASP, .NET databases: SQlite, mongoDB, MySQL, web servers: Node.js, IIS, hardware programming: Arduino, Raspberry pi, Ethernet Shield, GPS and GSM/GPRS, ESP8266, control and monitor systems for data acquisition and programming. I would like to thank God for helping me reviewing this book; my wife, Mayte, my sons, Ruben and Dario, for their support while writing this book and in general for their support in all my projects. To my parents and my brother and sister, whom I love. www.PacktPub.com For support files and downloads related to your book, please visit (cid:88)(cid:88)(cid:88)(cid:16)(cid:49)(cid:66)(cid:68)(cid:76)(cid:85)(cid:49)(cid:86)(cid:67)(cid:16)(cid:68)(cid:80)(cid:78). Did you know that Packt offers eBook versions of every book published, with PDF and ePub files available? You can upgrade to the eBook version at (cid:88)(cid:88)(cid:88)(cid:16)(cid:49)(cid:66)(cid:68)(cid:76)(cid:85)(cid:49)(cid:86)(cid:67)(cid:16)(cid:68)(cid:80)(cid:78) and as a print book customer, you are entitled to a discount on the eBook copy. Get in touch with us at (cid:84)(cid:70)(cid:83)(cid:87)(cid:74)(cid:68)(cid:70)(cid:33)(cid:81)(cid:66)(cid:68)(cid:76)(cid:85)(cid:81)(cid:86)(cid:67)(cid:16)(cid:68)(cid:80)(cid:78) for more details. At (cid:88)(cid:88)(cid:88)(cid:16)(cid:49)(cid:66)(cid:68)(cid:76)(cid:85)(cid:49)(cid:86)(cid:67)(cid:16)(cid:68)(cid:80)(cid:78), you can also read a collection of free technical articles, sign up for a range of free newsletters and receive exclusive discounts and offers on Packt books and eBooks. (cid:73)(cid:85)(cid:85)(cid:81)(cid:84)(cid:28)(cid:17)(cid:17)(cid:88)(cid:88)(cid:88)(cid:16)(cid:81)(cid:66)(cid:68)(cid:76)(cid:85)(cid:81)(cid:86)(cid:67)(cid:16)(cid:68)(cid:80)(cid:78)(cid:17)(cid:78)(cid:66)(cid:81)(cid:85) (cid:2) (cid:2) (cid:2) (cid:2) (cid:2) (cid:2) (cid:2) (cid:2) (cid:2) (cid:2) (cid:2) (cid:2) (cid:2) (cid:2) (cid:2) (cid:2) (cid:2) (cid:2) (cid:2) (cid:2) (cid:2) (cid:2) (cid:2) (cid:2) Get the most in-demand software skills with Mapt. Mapt gives you full access to all Packt books and video courses, as well as industry-leading tools to help you plan your personal development and advance your career. Why subscribe? Fully searchable across every book published by Packt Copy and paste, print, and bookmark content On demand and accessible via a web browser Customer Feedback Thanks for purchasing this Packt book. At Packt, quality is at the heart of our editorial process. To help us improve, please leave us an honest review on this book's Amazon page at (cid:73)(cid:85)(cid:85)(cid:81)(cid:84)(cid:28)(cid:17)(cid:17)(cid:88)(cid:88)(cid:88)(cid:16)(cid:66)(cid:78)(cid:66)(cid:91)(cid:80)(cid:79)(cid:16)(cid:68)(cid:80)(cid:78)(cid:17)(cid:69)(cid:81)(cid:17)(cid:19)(cid:25)(cid:26)(cid:24)(cid:22)(cid:24)(cid:19)(cid:26)(cid:20)(cid:57). (cid:2) (cid:2) (cid:2) (cid:2) (cid:2) (cid:2) (cid:2) (cid:2) (cid:2) (cid:2) (cid:2) (cid:2) (cid:2) (cid:2) (cid:2) (cid:2) (cid:2) (cid:2) (cid:2) (cid:2) (cid:2) (cid:2) If you'd like to join our team of regular reviewers, you can e-mail us at (cid:68)(cid:86)(cid:84)(cid:85)(cid:80)(cid:78)(cid:70)(cid:83)(cid:83)(cid:70)(cid:87)(cid:74)(cid:70)(cid:88)(cid:84)(cid:33)(cid:81)(cid:66)(cid:68)(cid:76)(cid:85)(cid:81)(cid:86)(cid:67)(cid:16)(cid:68)(cid:80)(cid:78). We award our regular reviewers with free eBooks and videos in exchange for their valuable feedback. Help us be relentless in improving our products! Table of Contents Preface 1 Chapter 1: SSIS Setup 7 Introduction 7 SQL Server 2016 download 7 Getting ready 7 How to do it... 8 Installing JRE for PolyBase 13 Getting ready 13 How to do it... 14 How it works... 20 Installing SQL Server 2016 20 Getting ready 20 How to do it... 21 SQL Server Management Studio installation 42 Getting ready 42 How to do it... 42 SQL Server Data Tools installation 46 Getting ready 47 How to do it... 47 Testing SQL Server connectivity 55 Getting ready 55 How to do it... 56 Chapter 2: What Is New in SSIS 2016 58 Introduction 58 Creating SSIS Catalog 59 Getting ready 59 How to do it... 60 Custom logging 64 Getting ready 65 How to do it... 65 How it works... 68 There's more... 68 Create a database 69 Create a simple project 70