Book Image

SQL Server 2017 Integration Services Cookbook

By : Christian Cote, Dejan Sarka, David Peter Hansen, Matija Lah, Samuel Lester, Christo Olivier
Book Image

SQL Server 2017 Integration Services Cookbook

By: Christian Cote, Dejan Sarka, David Peter Hansen, Matija Lah, Samuel Lester, Christo Olivier

Overview of this book

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.
Table of Contents (18 chapters)
Title Page
About the Authors
About the Reviewers
Customer Feedback

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.