Packt+ | Advance your knowledge in tech

You're reading from SQL Server 2017 Integration Services Cookbook Powerful ETL techniques to load and transform data from almost any source

Product type Paperback

Published in Jun 2017

Publisher Packt

ISBN-13 9781786461827

Length 558 pages

Edition 1st Edition

Languages

SQL

Tools

SQL Server

Concepts

Database Administration

Authors (6):

Cote

Dejan Sarka

Lah

David Peter Hansen

Samuel Lester

Olivier

+2 more

Preface

1. SSIS Setup FREE CHAPTER

2. What Is New in SSIS 2016

3. Key Components of a Modern ETL Solution

4. Data Warehouse Loading Techniques

5. Dealing with Data Quality

6. SSIS Performance and Scalability

7. Unleash the Power of SSIS Script Task and Component

8. SSIS and Advanced Analytics

9. On-Premises and Azure Big Data Integration

10. Extending SSIS Custom Tasks and Transformations

11. Scale Out with SSIS 2017

We'll now configure the SSIS catalog and workers to be able to use scale out executions with SSIS.

This recipe assumes that you've installed SQL Server 2017, SSIS in scale out mode as well as SSMS 17.1 or later.

Open SQL Server Management Studio and connect to the newly installed SQL Server 2017 instance.
In the Object Explorer, right-click on the Integration Services Catalogs node and select Create Catalog.
The Create Catalog window appears. As shown in the following screenshot, check the Enable this server as SSIS scale out master option as well as providing a password for the catalog. Click on OK when finished.

Still in SSMS, run the following SQL statements. The first statement lists the workers available. Copy the WorkerAgentId into the clipboard by right-clicking on the value in the grid and select Copy. Type the second SQL statement and use the clipboard's content as parameter (the grayed-out shape in the screenshot...