Jul 242012
 

In the new SSIS 2012 catalog’s project deployment model, by default if you execute a package with the Execute Package control flow task, or through the new API’s start_execution, the call will be made asynchronously.

In order to call the package synchronously, and therefore keep the caller waiting until the package finishes, you have to set a parameter value to true. This parameter, SYNCHRONIZED, is very, very sparsely documented on the ‘Net, but you can see it here assuming you’ve already executed some packages in your catalog:

SELECT DISTINCT parameter_name
  FROM SSISDB.catalog.execution_parameter_values  
 WHERE object_type = 50 -- 50 == execution parameter

To call it, simply call catalog.set_execution_parameter_values before calling catalog.start_execution and set the SYNCHRONIZED parameter to true (1). Like so:

EXEC [SSISDB].[catalog].[set_execution_parameter_value] 
        @execution_id,  -- execution_id from catalog.create_execution
        @object_type=50, 
        @parameter_name=N'SYNCHRONIZED', 
        @parameter_value= 1; -- turn on synchronized execution

While I’m here, the other execution parameters are as follows:

  • DUMP_ON_EVENT
  • DUMP_EVENT_CODE
  • LOGGING_LEVEL
  • CALLER_INFO
  • DUMP_ON_ERROR

DUMP_ON_EVENT – Boolean – 0* | 1 – Used with DUMP_EVENT_CODE, this signals to the SSIS engine to create a dump when the specified DUMP_EVENT_CODEs are encountered.

DUMP_EVENT_CODE – String – “”* – Used with DUMP_ON_EVENT, a semi-colon delimited list of event codes that will generate dump files if encountered. If we wanted to capture login failures and unable to connect event codes, specify respectively: 0x80040E4D;0x80004005.

LOGGING_LEVEL – Integer – 0 | 1* | 2 | 3 – Sets the logging level for this execution. The values represent no logging, basic, performance, and verbose levels respectively.

CALLER_INFO – String – “”* – Used to pass-in additional information on the caller. Helpful when viewing the overview of an execution in the built-in reporting provided with the catalog.

DUMP_ON_ERROR – Boolean – 0* | 1 – If an error is encountered, create a dump.

* – default value

See below for a complete example of the above parameters.

DECLARE @execution_id BIGINT = 0;

-- Create a package execution
EXEC [SSISDB].[catalog].[create_execution] 
        @package_name=N'Package.dtsx', 
        @execution_id=@execution_id OUTPUT, 
        @folder_name=N'PhilsTest', 
        @project_name=N'Demo', 
        @use32bitruntime=False;

EXEC [SSISDB].[catalog].[set_execution_parameter_value] 
        @execution_id,  
        @object_type=50, 
        @parameter_name=N'SYNCHRONIZED', 
        @parameter_value=1; -- true

-- Set our package parameters
EXEC [SSISDB].[catalog].[set_execution_parameter_value] 
        @execution_id,  
        @object_type=50, 
        @parameter_name=N'DUMP_ON_EVENT', 
        @parameter_value=1; -- true

EXEC [SSISDB].[catalog].[set_execution_parameter_value] 
        @execution_id,  
        @object_type=50, 
        @parameter_name=N'DUMP_EVENT_CODE', 
        @parameter_value=N'0x80040E4D;0x80004005';

EXEC [SSISDB].[catalog].[set_execution_parameter_value] 
        @execution_id,  
        @object_type=50, 
        @parameter_name=N'LOGGING_LEVEL', 
        @parameter_value= 1; -- Basic

EXEC [SSISDB].[catalog].[set_execution_parameter_value] 
        @execution_id,  
        @object_type=50, 
        @parameter_name=N'CALLER_INFO', 
        @parameter_value= N'Phil''s Demo';

EXEC [SSISDB].[catalog].[set_execution_parameter_value] 
        @execution_id,  
        @object_type=50, 
        @parameter_name=N'DUMP_ON_ERROR', 
        @parameter_value=1; -- true

-- Start the package
EXEC [SSISDB].[catalog].[start_execution] 
        @execution_id;

To confirm that they were accepted, here is a screenshot from the execution overview report:
SSIS Catalog Execution Parameters

  6 Responses to “Quick Tip – Run SSIS 2012 packages synchronously and other execution parameters”

  1. Thanks for the info in this post it has been a huge help. My project is also using SSIS 2012 with project deployment.
    I’m using the Execute SQL script to execute the SSIS package in a scheduling tool and by using the SYNCHRONIZED parameter I am now able to get the scheduling tool to wait for the job to finish before proceeding to the next job.
    However if the SSIS package fails no return code is sent back to notify the scheduler the job has failed.
    Is there a parameter that can be used to capture or send this back?

  2. Thanks for the article. It’s silly how Microsoft made SSIS 2012 radically different than its predecessors, and then did their best to avoid documentation. I guess they want us to attend their training sessions

    I do have a question –
    You mention that the timeout isn’t handled well. I am having the same problem… I see no “TIMEOUT” parameter among the ones provided (like SYNCHRONIZED, DUMP_ERROR, etc.). Is there a way to extend the timeout of an SSISDB package call from within a Script Task?

    Thanks,
    -Mikah

  3. I am not coming to play a game, that I will be involved in alone, only to get disconnected when i lose internet connection.

  4. […] is synchronous or asynchronous, but there are some other server options you can set as well. Phil Brammer actually blogged about the options last year. You can also see the full list of options when you view the SSIS Catalog Execution […]

  5. Hi, good article!

    Can you help me with one thing.
    I’m using similar ssis package execution in my project.
    I have a job with few steps, one of them (step 1) run ssis package synchronously, then all other steps.
    EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id1, @object_type=50, @parameter_name=N’SYNCHRONIZED’, @parameter_value=1

    And everything worked fine.
    But recently I found out that if for some reason package will fail it won’t prevent failing the whole job and continue execution
    Do you know is there any way to configure this execution in such way that when the ssis package fail, it will throw error and prevent my process from further running.

    I would really appreciated for any help.

    Thanks,
    Alex

  6. […] execution before returning.  (Note: For additional information about execution parameters, check out this post by Phil Brammer).  Second, regardless of whether you set the SYNCHRONIZED parameter, the T-SQL command will […]

 Leave a Reply

(required)

(required)

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

css.php