Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Execute SSIS Package in Scale Out Mode - SQL Server 2017


By:   |   Last Updated: 2017-07-27   |   Comments   |   Related Tips: More > 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.

SSIS Scale out  Features of SQL Server 2017

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.

SSIS Execution till SQL Server 2016

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

  • Execute
  • Execute in Scale Out
SSIS Execution in SQL Server 2017

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.

SSIS Execution in SQL Server 2017

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.

SSIS Execution in SQL Server 2017

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.

SSIS Execution in SQL Server 2017

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

SSIS Execution in SQL Server 2017

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.

SSIS Execution in SQL Server 2017

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.

SSIS Execution in SQL Server 2017

Now click on OK to execute the package.

SSIS Execution in SQL Server 2017

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', @[email protected]_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', 
@[email protected]

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:

Value Logging Type Description
0 None Logging is turned off. Only the package execution status is logged
1 Basic All events are logged, except custom and diagnostic events. This is the default value
2 Performance Only performance statistics, and OnError and OnWarning events, are logged.
4 Verbose All events are logged, including custom and diagnostic events.

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', @[email protected]_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', 
@[email protected]

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
SSIS Execution in SQL Server 2017

Execution Monitoring

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

SSIS Execution in SQL Server 2017

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

SSIS Execution in SQL Server 2017

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

SSIS Execution in SQL Server 2017

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
SSIS Execution in SQL Server 2017

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 Execution in SQL Server 2017

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
SSIS Execution in SQL Server 2017
Next Steps


Last Updated: 2017-07-27


next webcast button


next tip button



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

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools