Scenario:
(wait until ssis package finished execution)
Once your packages are stored in the SSIS Catalog store, 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)?
Solution:
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
Job done.
Example:
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].[set_execution_parameter_value] @execution_id, @object_type=50, @parameter_name=N’SYNCHRONIZED‘, @parameter_value=1
EXEC [SSISDB].[catalog].[start_execution] @execution_id
GO
The following code stop continuing going into the next stage if the package execution fail:
DECLARE @PackageStatus nvarchar(100);
WITH ctePackageStatus
AS
(
SELECT CASE [status]
WHEN 1 THEN ‘created’
WHEN 2 THEN ‘running’
WHEN 3 THEN ‘canceled’
WHEN 4 THEN ‘failed’
WHEN 5 THEN ‘pending’
WHEN 6 THEN ‘ended unexpectedly’
WHEN 7 THEN ‘successful’
WHEN 8 THEN ‘stopping’
WHEN 9 THEN ‘completed’
END AS [status_text], *
FROM [SSISDB].[catalog].[executions]
WHERE [execution_id] = @execution_id
)
SELECT @PackageStatus=status_text FROM ctePackageStatus;
IF(@PackageStatus<>’successful’) RAISERROR(‘Package failed to complete successfully’, 18,1);
Masud Ahmed 