Search icon CANCEL
Subscription
0
Cart icon
Cart
Close icon
You have no products in your basket yet
Save more on your purchases!
Savings automatically calculated. No voucher code required
Arrow left icon
All Products
Best Sellers
New Releases
Books
Videos
Audiobooks
Learning Hub
Newsletters
Free Learning
Arrow right icon
SQL Server 2017 Integration Services Cookbook
SQL Server 2017 Integration Services Cookbook

SQL Server 2017 Integration Services Cookbook: Powerful ETL techniques to load and transform data from almost any source

By Matija Lah , Samuel Lester , Christo Olivier , David Peter Hansen , Christian Cote , Dejan Sarka
$51.99 $35.99
Book Jun 2017 558 pages 1st Edition
eBook
$51.99 $35.99
Print
$65.99
Subscription
Free Trial
Renews at $15.99p/m
eBook
$51.99 $35.99
Print
$65.99
Subscription
Free Trial
Renews at $15.99p/m

What do you get with eBook?

Product feature icon Instant access to your Digital eBook purchase
Product feature icon Download this book in EPUB and PDF formats
Product feature icon Access this title in our online reader with advanced features
Product feature icon DRM FREE - Read whenever, wherever and however you want
Table of content icon View table of contents Preview book icon Preview Book

SQL Server 2017 Integration Services Cookbook

Chapter 1. SSIS Setup

In this chapter, we will cover the following recipes:

  • SQL Server 2016 download
  • Installing JRE for PolyBase
  • Installing SQL Server 2016
  • SQL Server Management Studio installation
  • SQL Server Data Tools installation
  • Test SQL Server connectivity

Introduction


This chapter will cover the basics of how to install SQL Server 2016 to properly go through the examples in this book. The version of SQL Server used through out this book is the Developer edition of SQL Server 2016. It's available for free as long as you subscribe to Visual Studio Dev Essentials.

SQL Server 2016 download


Following are the steps to download and install SQL Server 2016.

Getting ready

You need to have access to the internet for this recipe.

How to do it...

  1. Open your browser and paste this link: https://www.visualstudio.com/dev-essentials/. The following page appears in your browser:

  1. Click on Sign in visible at the right (top) to log in Visual Studio Dev Essentials. If you don't have an existing subscription, you can create one by clicking on the Join or access now button in the middle of the page, as shown in the following screenshot:

  1. You are directed to the My Information page. Click on My Benefits at the top of the page to access the download section as shown in the following screenshot:

  1. Click on the Download link in the Microsoft SQL Server Developer Edition tile as highlighted in the following screenshot:

  1. This will redirect you to the SQL Server 2016 Developer Edition page. Click on the green arrow to start downloading the ISO file as shown in the following screenshot:

  1. Due to its pretty large size, the file may take some time to download. The following screenshot is shows 44% done and 10 seconds left to download. This is due to the fact that the file is being downloaded on an Azure VM. It might take longer for you to download it. Depending on your browser, you should see the file downloading as in the following screenshot:

  1. Don't mount the ISO file for now. We have to install an external component described in the next section before we proceed with the installation of SQL Server.

Installing JRE for PolyBase


Java Runtime Engine (JRE) is required for PolyBase installations. SQL Server PolyBase is the technology that allows data integration from other sources other than SQL Server tables. PolyBase is used to access data stored in Hadoop File System (HFS) or Windows Azure Storage Blob (WASB).

As you will see later in this book, SSIS can now interact with these types of storage natively but having PolyBase handy can save us valuable time in our ETL.

Getting ready

For this recipe you will need to have access to the internet and have administrative rights on your PC to install JRE.

How to do it...

  1. To download JRE, follow this link: http://www.oracle.com/technetwork/java/javase/downloads/index.html . You will see the screen shown in the following screenshot:

This directs you to the Java SE Download at Oracle.

  1. Click the download link in the JRE section as shown in the following screenshot:

  1. You must accept the license agreement to be able to select a file to download. Select Accept License Agreement as indicated in the following screenshot:

  1. Since SQL Server 2016 only exists in a 64-bit version, download the 64-bit JRE. The version of Java SE runtime environment might be different from the one show in the screenshot, which is the one available at the time this book was written:.

  1. Once downloaded, launch the installer. Click on Run as shown in Edge browser. Otherwise, go to your Downloads folder and double-click on the file you just downloaded (jre-8U102-windows-x64.exe in our case); you will see the following window:

  1. The Oracle JRE installation starts. Click on Install. The following screen appears. It indicates the progress of the JRE installation.

  1. Once the installation is completed, click on Close to quit the installer:

You are now ready to proceed to install SQL Server 2016. We'll do that in the next section.

How it works...

Microsoft integrated PolyBase in SQL Server 2016 to connect almost natively to the Hadoop and NoSQL platforms. Here are the technologies it allows us to connect to:

  • HDFS (Hortonworks and Cloudera)
  • Azure Blob Storage

Since Hadoop is using Java technology, JRE is used to interact with its functionalities.

Installing SQL Server 2016


This section will go through the installation of SQL Server engine, which will host the database objects used throughout this book.

These are the features available for SQL Server setup:

  • Database engine: It is the core of SQL Server. It manages the various database objects such as tables, views, stored procedures, and so on.
  • Analysis services: It allows us to create a data semantic layer that eases data consumption by users.
  • Reporting services (native): It allow us to create various reports, paginated, mobile, and KPI's for data consumption.
  • Integration services: It is the purpose of this book, SQL Server data movement service.
  • Management tools: We'll talk about these in the next section.
  • SQL Server Data Tools: We'll talk about these in the next section.

Getting ready

This recipe assumes that you have downloaded SQL Server 2016 Developer Edition and you have installed Oracle JRE.

How to do it...

  1. The first step is to open the ISO file that you downloaded from the Microsoft Visual Studio Dev Essentials website as described in the SQL Server 2016 download recipe. If you're using Windows 7, you'll need to extract the ISO file into a folder. Third-party file compression utilities such as WinRAR, WinZip, or 7-Zip (and there are many more) can handle ISO file decompression. The setup files will be uncompressed in the folder of your choice. In other versions of Windows such as Windows 8.1, Windows 10, or Windows Server 2012 and beyond, simply double-click on the ISO file that you have downloaded previously and a new drive will appear in Windows Explorer.
  2. Double-click on the file named Setup.exe to start the SQL Server installation utility. The features we're going to install are as follows:
    • New SQL Server stand-alone installation or adding features to an existing installation: This will install a local instance (service) of SQL Server on your PC
    • SQL Server Management Tools: The tools used to create, query, and manage SQL Server objects
    • Install SQL Server Data Tools: This contains Visual Studio templates to develop and deploy SQL Server databases, integration services packages, analysis service cubes, and reporting services

  1. From the installation utility, select the New SQL Server stand-alone installation... option as shown in the following screenshot. A new SQL Server setup window opens.

  1. The Product Key page allows us to specify an edition to install. Since we're going to use the free Developer Edition, click Next to go to the next page, as shown in the following screenshot:

  1. Accept the license terms and click Next to go to the next page, as shown in the following screenshot:

  1. In this step, the SQL Server setup will check for product updates and will integrate itself into Windows update checks that are done regularly on your machine. This step is not mandatory but it's better to use the latest code. Check Use Microsoft Update... and click Next, as shown in the following screenshot:

  1. Some updates might be found during setup. You can get more information on these updates by clicking the link in the More Information column. Click Next to install the updates, as shown in the following screenshot:

  1. This step simply checks to make sure that the latest version of SQL Server is installed. Click Next once the setup files are installed, as shown in the following screenshot:

  1. SQL Server setup will check several rules to ensure that the computer where we want to install it is setup properly. You might get a warning due to Windows firewall rules. This tells you that the port (1433 by default) is not open and SQL Server won't be available from outside your PC. Don't worry about it. Since we'll be using SQL Server from our PC only, we do not need to open any ports for now. Click Next to advance to the feature selection page, as shown in the following screenshot:

  1. Select all features checked in the preceding screenshot and click Next, as shown in the following screenshot:

  1. Instance configuration allows to specify a name for the SQL Server service. This is done by selecting the Named instance radio button. Since we'll only use one SQL Server instance, leave Default instance selected and click Next, as shown in the following screenshot:

  1. This page allows SQL Server to be part of a PolyBase scale out group. Since we're only setting up SQL Server PolyBase to be used by one instance, leave the default Use this SQL Server as standalone PolyBase-enabled instance and click Next, as shown in the following screenshot:

  1. Now for server configuration. This step allows us to specify distinct or specific service accounts. Since we're installing SQL Server on a single development machine, we'll use the default accounts, as shown in the following screenshot:

  1. Click on the Collation tab as highlighted on the preceding screenshot. The default collation used by SQL server is SQL Latin1_General_CP1_CI_AS. This is a legacy collation. The choice of the collation is important for character string columns. The latest (fewer bugs) collation is Latin1_General_100. The last characters CI and AS are for case-insensitive and accent-sensitive, respectively.
  2. We'll change the collation defaults. Click Customize... at the end of SQL Latin1_General_CP1_CI_AI, as shown in the following screenshot:

  1. As stated previously, we'll use the Windows collation designator Latin1_General_100. Uncheck Accent-sensitive. This allows SQL Server to sort character columns without using accentuated characters. For example, suppose that our application has a FirstName column and we have the following first names:
    • Joel
    • Joël
  1. If we query SQL Server filtering on FirstName = 'Joel' with the Accent-sensitive collation option, we end up retrieving the value Joel only. If we do not select the Accent-sensitive collation option, we will get both values.
  2. Click OK when done to return to the previous screen. We'll do the same for analysis services; click Customize... to customize analysis service collation, as shown in the following screenshot:

  1. Again, choose Latin1_General_100 in Collation designator and uncheck the Accent-sensitive checkbox. Click OK to return to the previous screen. Click on Next, as shown in the following screenshot:

  1. This will direct you to the following screen. For the database engine configuration, we'll use Mixed Mode to allow us to use SQL Server logins and Windows logins. The default authentication is Windows authentication mode, which is more secure than SQL Server authentication because it uses the Kerberos security protocol, password, and account lockout policies, and password expiration. Make sure you use strong passwords for SQL Server logins. By default, password policy, password expiration, and user must change password at next login are turned on also for SQL Server login. You should not disable the password policy and the password expiration. Select the Mixed Mode radio box and enter a password for the SA account. Click on Add Current User as shown in the screenshot to add your Windows account as an administrator of the instance. You'll have all rights on it. Click Next, which will direct you to the Analysis Services Configuration window, as shown in the following screenshot:

  1. For analysis services configuration, the Server Mode we'll use is Tabular Mode and again click on Add Current User as shown in the following screenshot to add your Windows account as an administrator of the service. Click Next.

  1. For the Reporting services configuration, leave the default values and click Next, as shown in the following screenshot:

  1. We're finally ready to install. Click Install to start the installation process, as shown in the following screenshot:

  1. The following screenshot shows the installation progress:

  1. Once the installation is complete, you get the following screen:

We're done. We just installed SQL Server 2016! In the next section, we'll install SQL Server Management Studio.

Note

The number, the order, and the appearance of the setup screens change slightly with every version of SQL Server, or even with a service pack. If you encounter a new screen not mentioned here, just use the default settings and proceed with the installation.

SQL Server Management Studio installation


SQL Server Management Studio is a separate download from SQL Server. This program will allow us, among other things, to create database objects and query SQL Server. Without this tool, we wouldn't be able to manage SQL Server databases easily.

Getting ready

This section assumes that you have installed SQL Server 2016.

How to do it...

  1. To download and install SQL Server Management Studio, click on Install SQL Server Management Tools, as shown in the following screenshot:

  1. The SSMS download page opens in your browser. Click Download SQL Server Management Studio (the latest version) to start the download process. Once downloaded, run the installation as shown in the following screenshot:

  1. Click Install, as shown in the following screenshot:

  1. This will direct you to the Microsoft SQL Server Management Studio installation screen as follows. The installation is in progress; it may take several minutes to complete.

  1. Click Close to close the installation wizard, as shown in the following screenshot:

SQL Server Data Tools installation


The last part of our SQL Server 2016 setup is to install SQL Server Data Tools. This will install a Visual Studio Shell that contains BI templates necessary for the following:

  • SQL Server integration services
  • SQL Server analysis services
  • SQL Server reporting services
  • Database object management

Getting ready

We'll use SSDT throughout this book to create, deploy, and maintain our SSIS packages and some databases.

How to do it...

  1. From the SQL Server 2016 setup utility, click on SQL Server Data Tools (SSDT). This will open the Download SQL Server Data Tools (SSDT) download page in your browser as shown in the following screenshot:

Here, there are two choices:

Install SSDT only: This is the simplest scenario. It only installs SSDT and a development shell.

Install Visual Studio and SSDT: You choose this if you plan to use source control inside Visual Studio or when you want to implement different types of development (.NET, Python, and so on) such as SSIS/SSAS/SSRS development. Since we'll talk about custom components in this book, we'll install Visual Studio Community Edition. This version is free for individuals.

  1. Click on the Download Visual Studio Community 2015 link to download the Visual Studio installer.
  2. Once downloaded, click on Run to launch the Visual Studio installer.
  3. Accept the default installation type and click Install to start the installation process. This will give you the following window:

  1. Once the installation is completed, since we haven't installed SSDT, don't launch Visual Studio yet. Close this window. We'll install SQL Server Data Tools first. Return to the browser window and click Download SQL Server Data Tools for Visual Studio 2015 as shown in the following screenshot:

  1. This will direct you to the SSDT download screen shown as follows:

  1. From the browser page that opens, choose English (United States). The SSDT setup executable file download starts. Since it's a small file, it takes only a few seconds to download. Once the download completes, click Run or double-click on the newly downloaded file to start SSDT installation. Accept the defaults and click Next to proceed to the next step, as shown in the following screenshot:

  1. Accept the license agreement by checking the I agree to the license terms and conditions and click Install, as shown in the following screenshot:

  1. The SSDT installer will download the necessary files and proceed to the installation, as shown in the following screenshot:

  1. Once the installation completes, you might have to restart your computer. If that's the case, restart it, as shown in the following screenshot. Once that's done, look for SQL Server Data Tools in your Start menu and launch it.

  1. From the File menu, select New Project. Once the New Project window appears, you will see Business Intelligence in the project templates, as shown in the following screenshot:

  1. Close SSDT; we're done with it for now.

Testing SQL Server connectivity


SQL Server Management Studio has been installed in this chapter in the SQL Server Management Studio installation recipe. We'll now test whether we're able to connect to our local instance.

Getting ready

This recipe assumes that you have successfully installed SQL Server 2016 Developer Edition as well as SQL Server Management Studio.

How to do it...

  1. Look for SQL Server Management Studio in your Start menu and launch it.
  2. Once the application opens, you should see your PC's name in the Server Name field. Click on Connect. SSMS will now connect to your local SQL Server instance, as shown in the following screenshot:

  1. You are now able to connect to your local SQL Server instance, and we're now ready to begin work! You will get the following screen:

Left arrow icon Right arrow icon
Download code icon Download Code

Key benefits

  • Acquaint yourself with all the newly introduced features in SQL Server 2017 Integration Services
  • Program and extend your packages to enhance their functionality
  • This detailed, step-by-step guide covers everything you need to develop efficient data integration and data transformation solutions for your organization

Description

SQL Server Integration Services is a tool that facilitates data extraction, consolidation, and loading options (ETL), SQL Server coding enhancements, data warehousing, and customizations. With the help of the recipes in this book, you’ll gain complete hands-on experience of SSIS 2017 as well as the 2016 new features, design and development improvements including SCD, Tuning, and Customizations. At the start, you’ll learn to install and set up SSIS as well other SQL Server resources to make optimal use of this Business Intelligence tools. We’ll begin by taking you through the new features in SSIS 2016/2017 and implementing the necessary features to get a modern scalable ETL solution that fits the modern data warehouse. Through the course of chapters, you will learn how to design and build SSIS data warehouses packages using SQL Server Data Tools. Additionally, you’ll learn to develop SSIS packages designed to maintain a data warehouse using the Data Flow and other control flow tasks. You’ll also be demonstrated many recipes on cleansing data and how to get the end result after applying different transformations. Some real-world scenarios that you might face are also covered and how to handle various issues that you might face when designing your packages. At the end of this book, you’ll get to know all the key concepts to perform data integration and transformation. You’ll have explored on-premises Big Data integration processes to create a classic data warehouse, and will know how to extend the toolbox with custom tasks and transforms.

What you will learn

[*] Understand the key components of an ETL solution using SQL Server 2016-2017 Integration Services [*] Design the architecture of a modern ETL solution [*] Have a good knowledge of the new capabilities and features added to Integration Services [*] Implement ETL solutions using Integration Services for both on-premises and Azure data [*] Improve the performance and scalability of an ETL solution [*] Enhance the ETL solution using a custom framework [*] Be able to work on the ETL solution with many other developers and have common design paradigms or techniques [*] Effectively use scripting to solve complex data issues

Product Details

Country selected

Publication date : Jun 30, 2017
Length 558 pages
Edition : 1st Edition
Language : English
ISBN-13 : 9781786461827
Vendor :
Microsoft
Category :

What do you get with eBook?

Product feature icon Instant access to your Digital eBook purchase
Product feature icon Download this book in EPUB and PDF formats
Product feature icon Access this title in our online reader with advanced features
Product feature icon DRM FREE - Read whenever, wherever and however you want

Product Details


Publication date : Jun 30, 2017
Length 558 pages
Edition : 1st Edition
Language : English
ISBN-13 : 9781786461827
Vendor :
Microsoft
Category :

Table of Contents

18 Chapters
Title Page Chevron down icon Chevron up icon
Credits Chevron down icon Chevron up icon
About the Authors Chevron down icon Chevron up icon
About the Reviewers Chevron down icon Chevron up icon
www.PacktPub.com Chevron down icon Chevron up icon
Customer Feedback Chevron down icon Chevron up icon
Preface Chevron down icon Chevron up icon
1. SSIS Setup Chevron down icon Chevron up icon
2. What Is New in SSIS 2016 Chevron down icon Chevron up icon
3. Key Components of a Modern ETL Solution Chevron down icon Chevron up icon
4. Data Warehouse Loading Techniques Chevron down icon Chevron up icon
5. Dealing with Data Quality Chevron down icon Chevron up icon
6. SSIS Performance and Scalability Chevron down icon Chevron up icon
7. Unleash the Power of SSIS Script Task and Component Chevron down icon Chevron up icon
8. SSIS and Advanced Analytics Chevron down icon Chevron up icon
9. On-Premises and Azure Big Data Integration Chevron down icon Chevron up icon
10. Extending SSIS Custom Tasks and Transformations Chevron down icon Chevron up icon
11. Scale Out with SSIS 2017 Chevron down icon Chevron up icon

Customer reviews

Top Reviews
Rating distribution
Empty star icon Empty star icon Empty star icon Empty star icon Empty star icon 0
(0 Ratings)
5 star 0%
4 star 0%
3 star 0%
2 star 0%
1 star 0%
Top Reviews
No reviews found
Get free access to Packt library with over 7500+ books and video courses for 7 days!
Start Free Trial

FAQs

How do I buy and download an eBook? Chevron down icon Chevron up icon

Where there is an eBook version of a title available, you can buy it from the book details for that title. Add either the standalone eBook or the eBook and print book bundle to your shopping cart. Your eBook will show in your cart as a product on its own. After completing checkout and payment in the normal way, you will receive your receipt on the screen containing a link to a personalised PDF download file. This link will remain active for 30 days. You can download backup copies of the file by logging in to your account at any time.

If you already have Adobe reader installed, then clicking on the link will download and open the PDF file directly. If you don't, then save the PDF file on your machine and download the Reader to view it.

Please Note: Packt eBooks are non-returnable and non-refundable.

Packt eBook and Licensing When you buy an eBook from Packt Publishing, completing your purchase means you accept the terms of our licence agreement. Please read the full text of the agreement. In it we have tried to balance the need for the ebook to be usable for you the reader with our needs to protect the rights of us as Publishers and of our authors. In summary, the agreement says:

  • You may make copies of your eBook for your own use onto any machine
  • You may not pass copies of the eBook on to anyone else
How can I make a purchase on your website? Chevron down icon Chevron up icon

If you want to purchase a video course, eBook or Bundle (Print+eBook) please follow below steps:

  1. Register on our website using your email address and the password.
  2. Search for the title by name or ISBN using the search option.
  3. Select the title you want to purchase.
  4. Choose the format you wish to purchase the title in; if you order the Print Book, you get a free eBook copy of the same title. 
  5. Proceed with the checkout process (payment to be made using Credit Card, Debit Cart, or PayPal)
Where can I access support around an eBook? Chevron down icon Chevron up icon
  • If you experience a problem with using or installing Adobe Reader, the contact Adobe directly.
  • To view the errata for the book, see www.packtpub.com/support and view the pages for the title you have.
  • To view your account details or to download a new copy of the book go to www.packtpub.com/account
  • To contact us directly if a problem is not resolved, use www.packtpub.com/contact-us
What eBook formats do Packt support? Chevron down icon Chevron up icon

Our eBooks are currently available in a variety of formats such as PDF and ePubs. In the future, this may well change with trends and development in technology, but please note that our PDFs are not Adobe eBook Reader format, which has greater restrictions on security.

You will need to use Adobe Reader v9 or later in order to read Packt's PDF eBooks.

What are the benefits of eBooks? Chevron down icon Chevron up icon
  • You can get the information you need immediately
  • You can easily take them with you on a laptop
  • You can download them an unlimited number of times
  • You can print them out
  • They are copy-paste enabled
  • They are searchable
  • There is no password protection
  • They are lower price than print
  • They save resources and space
What is an eBook? Chevron down icon Chevron up icon

Packt eBooks are a complete electronic version of the print edition, available in PDF and ePub formats. Every piece of content down to the page numbering is the same. Because we save the costs of printing and shipping the book to you, we are able to offer eBooks at a lower cost than print editions.

When you have purchased an eBook, simply login to your account and click on the link in Your Download Area. We recommend you saving the file to your hard drive before opening it.

For optimal viewing of our eBooks, we recommend you download and install the free Adobe Reader version 9.