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:

SSISSynch1

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:

SSISSynch2

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:

SSISSynch3

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:

SSISSynch4

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:

SSISSynch5

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!

:{>

Andy Leonard

andyleonard.blog

Christian, husband, dad, grandpa, Data Philosopher, Data Engineer, SSIS and Biml guy. I was cloud before cloud was cool. :{>

6 thoughts on “The SYNCHRONIZED SSIS Execution Parameter

  1. Hi Andy, thanks for the article.

    How do I make sql server agent execute my package with synchronised = false. When I execute the package directly, it does this but sql server agent is executing as True by default which is causing problems.

  2. Hi Adam,

    You can start SSIS packages using T-SQL directly or in a stored procedure. An example is the custom.execute_catalog_package stored procedure I include as part of SSIS Framework Community Edition (https://dilmsuite.com/ssis-framework-community-edition). You can edit execute_catalog_package to include a stored procedure parameter for the SYNCHRONIZED value – this is what we do for the Commercial and Enterprise Editions of the Framework – defaulted to 1 (true) and then override that stored procedure parameter value whenever you want to set the SYNCHRONIZED execution parameter to false.

    Hope this helps,
    Andy

  3. Hi Andy. One of the ways to exec catalog based ssis pkgs from powershell is shown below. Using this format, do you happen to know if there is a way to set sync=true? We already know how to exec procs from powershell etc overriding the sync flag.

    (Get-ChildItem SQLSERVER:\SSIS\localhost\Default\Catalogs\SSISDB\Folders\Project1Folder\Projects\’Integration Services Project1’\Packages\ |
    WHERE { $_.Name -eq ‘Package.dtsx’ }).Execute(“false”, $null)

  4. Hello. Great explanation. Thanks. Could you tell me please if SYNCHRONIZED parameter could be set with SQL Server Agent interface, not just T-SQL.
    Thanks.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.