Execute SSIS Package in Scale Out Mode - SQL Server 2017

By: Rajendra Gupta   |   Comments   |   Related: > Integration Services Configuration Options

Problem

In my previous tips SQL Server vNext (SQL Server 2017) SSIS Scale Out Feature and SQL Server 2017 SSIS Scale Out Configuration we have seen that SSIS Scale Out is new in SQL Server 2017. In this tip, we will see how to execute SSIS packages using the Scale Out SSIS functionality with both SSMS and T-SQL.

Solution

As we have seen in previous tips, SQL Server 2017 ScaleOutMaster and ScaleOutWorker is new in SQL Server 2017. So far we have learned how to install and configure both ScaleOutMaster and ScaleOutWorker using different parameters.

Before we proceed further, let's take a look at what the overall Scale Out feature looks like.

We can see here that ScaleOutMaster (top) contains SSISDB along with the SSIS master service. It also holds the configurations and the certificates (for the master and each worker). The SSIS master communicates with each of the worker machines.

Each SSIS ScaleOutWorker contains the SSIS worker service. Each worker machine contains its worker certificate along with the master certificate. Each worker machine also contains logs and configuration files as well.

Execute SSIS package in Scale Out mode using SSMS GUI

To move ahead with this tip, we should have a SSIS package configured under the SSIS Integration services catalogs. Please read these previous tips SSIS Project Deployment Model in SQL Server 2012 (Part 1 of 2) and SSIS Project Deployment Model in SQL Server 2012 (Part 2 of 2).

To execute a SSIS package from SSMS in previous versions of SQL Server, in the Object Explorer we expand the tree to display the nodes under Integration Services Catalogs and right-click the package and select Execute as shown below.  This is the only option to execute the package.

If we look at SQL Server 2017, it provides two options:

• Execute
• Execute in Scale Out

To execute the package in Scale Out mode, click on Execute in Scale Out.

This opens a new window - Execute Package in Scale Out as shown below.  We have to settings to configure, Package Selection and Machine Selection.

Package Selection

On the Package Selection page, we can select multiple packages to run and set the environment, parameters, connection managers, and advanced options for each package. Let's select a package and configure.

Connection Managers

The connection manager allows us to set connection properties.

In the Advanced tab, we can set a Scale Out option called Retry count. It sets the number of times a package execution will retry if it fails. By default it is set to 0.

To change the value, select from the drop down as shown below.

Machine Selection

On the Machine Selection page, we can select the Scale Out Worker machines to run the package. By default, any machine is allowed to run the package.

If we have multiple worker machines and want to execute the package on selected workers, we can select them from the worker list. To do so, remove the check box Allow any machine to execute the selected packages and select the worker machine.

Now click on OK to execute the package.

Execute SSIS package in Scale Out mode using T-SQL

I will explain each section of the code and then at the end pull it all together.

Create Execution

We need to call [catalog].[create_execution] for each package.

We need to set the parameter @runincluster = True, if not all Scale Out Worker machines are allowed to run the package.  Also, set parameter @useanyworker = False.

DECLARE @execution_id bigint
EXEC [SSISDB].[catalog].[create_execution] @package_name=N'Package.dtsx', @execution_id=@execution_id OUTPUT, 
@folder_name=N'Test', @project_name=N'Test', @use32bitruntime=False, @reference_id=Null, @useanyworker=False, 
@runincluster=True

Set Execution Parameter Value

This step is to set parameter values for an instance of execution in the Integration Services catalog.

SELECT @execution_id
DECLARE @var0 smallint = 1
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id, @object_type=50, @parameter_name=N'LOGGING_LEVEL', 
@parameter_value=@var0

For the following parameters, @object_type needs to be set to 50 to use for @parameter_name:

• LOGGING_LEVEL
• CUSTOMIZED_LOGGING_LEVEL
• DUMP_ON_ERROR
• DUMP_ON_EVENT
• DUMP_EVENT_CODE
• CALLER_INFO
• SYNCHRONIZED

Below are the values for the @var0 which defines the logging level:

Define Workers

In this step we need to define the worker machine on which the package will be executed. If we want to run the package on all worker instances, this step is not required.

Below is the procedure which should be run if we want to define the workers. We need to pass @workeragent_id which can be obtained by querying [SSISDB].[internal].[worker_agents].

The following command will set the specific @workeragent_id.

EXEC [SSISDB].[catalog].[add_execution_worker] @execution_id, @workeragent_id=N'3cbd1f52-62b5-4dce-9bb6-64aa170a5e42'

Start Execution

In this step we will start execution of the package.

EXEC [SSISDB].[catalog].[start_execution] @execution_id, @retry_count=0

Complete Code

If we combine all steps above the complete query is:

DECLARE @execution_id bigint

EXEC [SSISDB].[catalog].[create_execution] @package_name=N'Package.dtsx', @execution_id=@execution_id OUTPUT, 
@folder_name=N'Test', @project_name=N'Test', @use32bitruntime=False, @reference_id=Null, @useanyworker=False, 
@runincluster=True

SELECT @execution_id
DECLARE @var0 smallint = 1

EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id, @object_type=50, @parameter_name=N'LOGGING_LEVEL', 
@parameter_value=@var0

EXEC [SSISDB].[catalog].[add_execution_worker] @execution_id, @workeragent_id=N'3cbd1f52-62b5-4dce-9bb6-64aa170a5e42'

EXEC [SSISDB].[catalog].[start_execution] @execution_id, @retry_count=0
GO

Execution Monitoring

To monitor package execution using SQL Server Management Studio, right click on the project and select Reports > Standard Reports > All Executions.

This gives the execution details with the status of success, failure, etc.

If you click on Overview, it gives more information as shown below.

Similarly if we want to monitor package execution using a query we can use the code below.

SELECT 
   e.execution_id, 
   e.created_time, 
   e.status,
   case 
      when status=1 Then 'Created'
      when status=2 Then 'Running'
      when status=3 Then 'Cancelled'
      when status=4 Then 'Failed'
      when status=5 Then 'Pending'
      when status=6 Then 'Ended Unexpectedly'
      when status=7 Then 'Succeeded'
      when status=8 Then 'Stopping'
      when status=9 then 'completed'
   end as Status,
   e.server_name as MasterServer,
   e.machine_name as WorkerMachine
FROM Catalog.executions e

Execution Monitoring Logs

As mentioned in the previous tip, we can also check the logs on the SSIS Master and SSIS Worker.

SSIS Master

The path will be similar to this:

• C:\Users\SSISScaleOutMaster140\AppData\Local\SSIS\ScaleOut\Master

SSIS Worker

The path will be similar to these:

• C:\Users\SSISScaleOutWorker140\AppData\Local\SSIS\ScaleOut\Tasks
• C:\Users\SSISScaleOutWorker140\AppData\Local\SSIS\ScaleOut\Agent

Next Steps

• This is very interesting and useful feature of SQL Server 2017. Take some time to explore how to use this in your environment.
• Learn more about SQL Server 2017 preview
• Read more about Logging Level Recommendations for the SQL Server Integration Services Catalog
• Note: SQL Server 2017 is still in preview, so some of these features or options may change when it is released.

About the author

Rajendra Gupta is a Consultant DBA with 14+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips