Table Of ContentChristian 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
