THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Davide Mauri

A place for my thoughts and experiences the Microsoft Data Platform

Execute a SSIS package in Sync or Async mode from SQL Server 2012

Today I had to schedule a package stored in the shiny new SSIS Catalog store that can be enabled with SQL Server 2012. (

Once your packages are stored here, they will be executed using the new stored procedures created for this purpose. This is the script that will get executed if you try to execute your packages right from management studio or through a SQL Server Agent job, will be similar to the following:

Declare @execution_id bigint

EXEC [SSISDB].[catalog].[create_execution] @package_name='my_package.dtsx', @execution_id=@execution_id OUTPUT, @folder_name=N'BI', @project_name=N'DWH', @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

DECLARE @var1 bit = 0
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  @object_type=50, @parameter_name=N'DUMP_ON_ERROR', @parameter_value=@var1

EXEC [SSISDB].[catalog].[start_execution] @execution_id

The problem here is that the procedure will simply start the execution of the package and will return as soon as the package as been started…thus giving you the opportunity to execute packages asynchrously from your T-SQL code. This is just *great*, but what happens if I what to execute a package and WAIT for it to finish (and thus having a synchronous execution of it)?

You have to be sure that you add the “SYNCHRONIZED” parameter to the package execution. Before the start_execution procedure:

exec [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  @object_type=50, @parameter_name=N'SYNCHRONIZED', @parameter_value=1

And that’s it Smile.


From the RC0, the SYNCHRONIZED parameter is automatically added each time you schedule a package execution through the SQL Server Agent. If you’re using an external scheduler, just keep this post in mind Smile.

Published Thursday, November 24, 2011 6:45 PM by Davide Mauri
Filed under: , ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



SSIS Junkie said:

This post relates to SQL Server code-named Denali which at the time of writing is a pre-release version

November 24, 2011 11:54 AM

Vaibu said:


I have implemented exactly the sameway. Is there a way we will know whether the package 'executed' successfully or failed. I have to return back to the calling client whether package succeded or failed.

Please suggest.

April 13, 2012 2:24 PM

vaibu said:

April 13, 2012 2:41 PM

Armando C. said:

I have made one stored procedure similar to you, My store receive three parameters "nombre" (Varchar type, without problems because I Set value from stored procedude), "FechaInicial" and "FechaFinal" (both are package's parameter in "String" format in SSIS)   When I try Execute the DTS show me an error.

I think is because the  @parameter_value clause expect the value in the "N'value" format, do you have some idea?  

Here are my code:

Create procedure [dbo].[Sp_Ejecuta_dts_2012]

@Nombre varchar(100),

@FechaInicial varchar(100),

@FechaFinal varchar(100)





Declare @execution_id bigint

EXEC [SSISDB].[catalog].[create_execution]

@package_name= @Nombre,

@execution_id=@execution_id OUTPUT,





Select @execution_id





declare @FechaInicial_ex  nvarchar(100)

set @FechaInicial_ex = @FechaFinal

EXEC [SSISDB].[catalog].[set_execution_parameter_value]




@parameter_value= @FechaInicial





declare @FechaFinal_ex  nvarchar(100)

set @FechaFinal_ex = @FechaFinal

EXEC [SSISDB].[catalog].[set_execution_parameter_value]




@parameter_value= @FechaInicial





exec [SSISDB].[catalog].[set_execution_parameter_value]







EXEC [SSISDB].[catalog].[start_execution] @execution_id


Execution sentence:

execute [dbo].[Sp_Ejecuta_dts_2012]


@FechaInicial= N'2013/01/01',

@FechaFinal = N'2013/01/01'

Error Message in SQL

"Msg 27147, Level 16, State 1, Procedure check_data_type_value, Line 26 The data type of the input value is not compatible with the data type of the 'String'.  

Someone idea, Thanks!!

June 12, 2013 8:13 PM

H said:

Thank you so much! When I realized what was happening I was dreading making this manually work. This was exactly what I needed.

November 17, 2016 7:57 PM

Waldemar Sarmiento said:

I have used this to load thousands of files in parallel and it works perfectly. The only problem is that I don't know when the last one will finish, which I need to process a cube. Any suggestion?

March 24, 2017 10:04 PM

Leave a Comment


About Davide Mauri

Director of Software Development & Cloud Infrastructure @ Sensoria, an innovative smart garments and wearable company. After more than 15 year playing with the Microsoft Data Platform, with a specific focus on High Performance databases, Business Intelligence, Data Science and Data Architectures, he's now applying all his skills to IoT, defining architectures to crunch numbers, create nice user experiences and provide meaningful insights, all leveraging Microsoft Azure cloud. MVP on Data Platform since 2006 he has a very strong background development and love both the ER model and OO principles. He is also a fan of Agile Methodology and Automation, which he tries to apply everywhere he can, to make sure that "people think, machines do".

This Blog


Privacy Statement