Execute SSIS Package in Scale Out Mode - SQL Server 2017
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.
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 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.
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.
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.
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.
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:
Below are the values for the @var0 which defines the logging level:
|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.|
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'
In this step we will start execution of the package.
EXEC [SSISDB].[catalog].[start_execution] @execution_id, @retry_count=0
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
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.
The path will be similar to this:
The path will be similar to these:
- 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
View all my tips