THE SQL Server Blog Spot on the Web

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

Davide Mauri

A place for my thoughts and experiences on SQL Server, Business Intelligence and .NET

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. (http://msdn.microsoft.com/en-us/library/hh479588(v=SQL.110).aspx)

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
GO

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.

PS

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

Comments

 

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:

Hi,

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)

as

Begin

--

--

Declare @execution_id bigint

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

@package_name= @Nombre,

@execution_id=@execution_id OUTPUT,

@folder_name=N'Folder',

@project_name=N'Project',

@use32bitruntime=False,

@reference_id=Null

Select @execution_id

end

--

--

begin

declare @FechaInicial_ex  nvarchar(100)

set @FechaInicial_ex = @FechaFinal

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

@execution_id,  

@object_type=20,

@parameter_name=N'FechaInicial',

@parameter_value= @FechaInicial

end

--

--

begin

declare @FechaFinal_ex  nvarchar(100)

set @FechaFinal_ex = @FechaFinal

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

@execution_id,  

@object_type=20,

@parameter_name=N'FechaFinal',

@parameter_value= @FechaInicial

end

--

--

begin

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

@execution_id,  

@object_type=50,

@parameter_name=N'SYNCHRONIZED',

@parameter_value=1

--

--

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

end

Execution sentence:

execute [dbo].[Sp_Ejecuta_dts_2012]

@Nombre='name.dtsx',

@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

Leave a Comment

(required) 
(required) 
Submit

About Davide Mauri

Davide Mauri - MCP, MCAD, MCDBA, MCT, MVP on SQL Server - has worked with SQL Server since version 6.5, and his interests cover the whole platform, from the Relational Engine to Analysis Services, from architecture definition to performance tuning. He also has a strong knowledge of XML, .NET and the Object Oriented Design principles, which allows him to have the correct vision and experience to handle development of complex business intelligence solutions. Having worked as a Microsoft Certified Teacher for many years, Davide is able to pass all his knowledge to his co-workers, allowing his team to deliver high-quality solutions. He currently works as a Mentor for SolidQ and can be found speaking in many Italian and internationals events.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement