The SYNCHRONIZED SSIS Execution Parameter

I was reminded today (again) that I need to blog about the SYNCHRONIZED SSIS execution parameter. I mentioned it last week while presenting at the PASS Summit 2015 and made a mental note to blog soon. Earlier today, as I was discussing features of the SSIS Catalog Compare utility, the topic came up again.

Credit where credit is due: I first learned about the SYNCHRONIZED SSIS execution parameter from Phil Brammer over at SSISTalk.com. Phil wrote Quick Tip – Run SSIS 2012 packages synchronously and other execution parameters way back in the summer of 2012. (Thank you, Phil!)

Synchronous SSIS Package Execution

I can hear you thinking, “What does the SYNCHRONIZED SSIS execution parameter do, Andy?” I’m glad you asked. When you right-click a package in the SSIS Catalog and then execute the package, the SSIS runtime grabs the XML, validates it, and executes it. If the runtime can find the package and (I believe) start the package execution, the caller will receive a “Success” from the SSIS runtime. The package may continue running for a long time afterwards. Worse, it may fail. You’ll have to review the logs to glean how long the package actually executed and whether it succeeded or failed.

Bummer.

Flipping the Bit

So, how does one set the SYNCHRONIZIED SSIS execution parameter? One way is to script the package execution by clicking the “Script” button on the Execute Package window, as shown here:

Clicking the Script button produces at least three T-SQL statements – calls to stored procedures in the SSISDB database’s catalog schema. I reformatted the text and it’s shown here:

Here’s a listing of the code you can copy and paste, edit, and use to execute SSIS packages on your server:

Declare @execution_id bigint
EXEC [SSISDB].[catalog].[create_execution]
   @package_name=N’Child1.dtsx’
, @execution_id=@execution_id OUTPUT
, @folder_name=N’FrameworkTestFolder’
, @project_name=N’FrameworkRestartabilityTest’
, @use32bitruntime=False
, @reference_id=Null

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].[start_execution] @execution_id
GO

There are three steps in the T-SQL listed and shown above:

1. Create an Intent to Execute – accomplished via the catalog.create_execution stored procedure, which returns an execution_id value.
2. Configure the Intent to Execute – accomplished here by a single call to the catalog.set_execution_parameter_value stored procedure and used to configure the LOGGING_LEVEL SSIS execution parameter for this execution (1 = Basic).
3. Execute – accomplished by the call to the catalog.start_execution stored procedure.

If you execute these stored procedures an SSIS package may execute (I write “may” because the parameters have to be properly configured to find and execute an SSIS package on your server, and I cannot see your machine from this blog… yet). If I view the SSIS Catalog’s Overview report for this package’s last execution, I can see the execution parameters in the “Parameters Used” tablix as shown here:

The SSIS execution parameters are named in upper case. For this reason, I do not name any of the other parameters in SSIS in all upper case. I want to be able to glance at this tablix and learn the values of the execution parameters (or the non-execution parameters). Note SYNCHORINZED is set to False.

But if we add code to the Configure the Intent to Execute step, we can change the SYNCHRONIZED value to True:

DECLARE @synchronized bit = 1

EXEC [SSISDB].[catalog].[set_execution_parameter_value]
  @execution_id
, @object_type=50
, @parameter_name=N’SYNCHRONIZED’
, @parameter_value=@synchronized

Executing the T-SQL now changes things, and we can view the changes in the Catalog Overview report:

Conclusion

When SQLAgent starts an SSIS package using the Integration Services Package job step type, it automatically overrides the SYNCHRONIZED SSIS execution parameter, setting it to True. Incidentally, SQLAgent also supplies a value for the CALLER_INFO parameter.

The SSIS Catalog provides many lesser-known and lesser-documented features to manage SSIS package execution. The SYNCHRONIZED execution parameter is but one cool nugget!

:{>