SQL Server 2017 Integration Services Cookbook

Christian Cote, Matija Lah, Dejan Sarka

• Publisher: Packt Publishing
• Year: 2017
• ISBN:
  9781786460875
• Edition: 1
• Topic: Ciencia de la computación
• Subtopic: Almacenamiento de datos

Harness the power of SQL Server 2017 Integration Services to build your data integration solutions with easeAbout This Book• 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 organizationWho This Book Is ForThis book is ideal for software engineers, DW/ETL architects, and ETL developers who need to create a new, or enhance an existing, ETL implementation with SQL Server 2017 Integration Services. This book would also be good for individuals who develop ETL solutions that use SSIS and are keen to learn the new features and capabilities in SSIS 2017.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 issuesIn DetailSQL 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.Style and approachThis cookbook follows a problem-solution approach and tackles all kinds of data integration scenarios by using the capabilities of SQL Server 2016 Integration Services. This book is well supplemented with screenshots, tips, and tricks. Each recipe focuses on a particular task and is written in a very easy-to-follow manner.

SSIS Performance and Scalability

This chapter covers the following recipes:

• Using SQL Server Management Studio to execute an SSIS package
• Using T-SQL to execute an SSIS package
• Using the DTExec command-line utility to execute an SSIS package
• Scheduling an SSIS package execution
• Using the cascading lookup pattern
• Using the lookup cache
• Using lookup expressions
• Determining the maximum number of worker threads in a data flow
• Using the master package concept
• Requesting an execution tree in SSDT
• Establishing a performance monitor session
• Configuring a performance monitor data collector set

Introduction

This chapter discusses the various methods of SSIS package execution, how to monitor the performance of running SSIS packages, and how to plan the utilization of resources for a given SSIS package. You will also learn how to use different techniques of acquiring reference data (also referred to as data look ups), and their impact on SSIS execution performance.

One of the objectives followed in the design of the SSIS execution engine is to maximize the use of resources on the system hosting SSIS package executions. In part, this is reflected in the capabilities of parallel execution of various operations; for instance, using multiple threads to perform data movements and transformations in the data flow, parallelizing the execution of operations in the control flow, or even scaling out the execution of packages to multiple hosting servers. Some of the techniques that you can use to improve resource utilization for SSIS executions are also discussed in this chapter.

SSIS execution techniques can be divided into two groups:

• On-demand execution is performed through intervention: A user creates, configures, and starts, the execution of an SSIS package. Typically, on-demand execution is used for SSIS solutions that only need to run when a user has decided that the work needs to be performed. Alternatively, SSIS packages are executed on demand when they have been integrated into existing systems, or tools, that the organization is using to perform its data management operations. In such cases, the execution is created, configured, and started, by a client application or a service.
• Scheduled execution is performed automatically: An administrator configures the execution of an SSIS package, where the execution starts automatically (in absence of user intervention). Typically, automated execution is performed on a schedule prepared in advance; for instance, using the SQL Server Agent, or another similar scheduling tool. Alternatively, the execution can also be started automatically based on certain other criteria defined in advance, the state of which can be determined automatically; for instance, a scheduled operation checks whether these criteria are met, and either starts the execution of an SSIS package, or completes without starting it.

When SSIS packages designed under the project deployment model are executed, the following three steps are performed:

1. An execution instance is created for the given SSIS package with the selected SSIS environment. If an environment is not associated with this execution, design-time values for any parameters, or any other configurable settings, are used.
2. After the execution has been created successfully, further settings can be configured; for instance, the logging level, the operational mode, or any other accessible property of the SSIS package. In this step, any configurable design-time settings can be overridden.

3. After all the execution properties have been set, the execution is started. This step actually invokes the execution of the package. Two modes of operation are supported:
   • In synchronous mode, the control is not returned to the caller until the execution of the package has completed (either successfully or with errors); this mode is useful for situations where the next operation in a sequence should not start until the preceding one has finished. When SSIS packages are set up as individual steps of an SQL Server Agent job, the next step must not begin until the preceding step has completed; therefore, SQL Server Agent uses synchronous executions.
   • In asynchronous mode, the control is returned to the caller immediately after the execution has started, allowing the caller to perform other work concurrently. This mode is useful when multiple packages, or multiple instances of the same package, need to be executed in parallel. In order to monitor the asynchronous execution of packages, the caller application needs to check the current state of the executions by querying the SSIS catalog. Typically, asynchronous executions are used when SSIS packages are integrated in the organization's existing systems, where other operations might need to be executed at the same time.

Steps 1 and 3 are mandatory, step 2 is optional. Both mandatory steps must be performed; otherwise, the execution cannot start. Step 2 needs to be performed for each additional setting, and can therefore be performed multiple times in a single execution.

SSIS deployment models

The project deployment model has been the default deployment model since SQL Server 2012; it perceives all packages of the same SSIS ...