Getting log messages from an SSIS package executed with an SQL query

I have a generic package (in a project called Common) that copies a file from a folder to another one.

Declare @executionID bigint
EXEC [SSISDB].[catalog].[create_execution] 
@package_name=N'copy_file.dtsx',
@execution_id=@executionID OUTPUT, 
@folder_name=N'SSIS_COMMON',
@project_name=N'Common', 
@use32bitruntime=False, 
@reference_id=Null
DECLARE @var0 sql_variant = N'D:\XXXX\XXX\XXX\target\to.xlsx'
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @executionID,  @object_type=30, @parameter_name=N'target_file',@parameter_value=@var0
DECLARE @var1 sql_variant = N'D:\XXXX\XXX\XXX\from_.xlsx'
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @executionID,  @object_type=30, @parameter_name=N'source_file',@parameter_value=@var1
EXEC [SSISDB].[catalog].[start_execution] @executionID
GO

The package is supposed to fail if, for instance, the source file (parameter "source_file") doesn't exist. However, when I run the package using the SQL query the result is always a success. Is there a way to make the query throw an error if the package fails?

Solution

The script you have just starts the package; it doesn't wait for it to finish, so it won't know if the task failed. This is the same idea of when you start an Agent job from T-SQL; it just starts the Agent job, and the starting was successful, so the script reports success (in starting the job).

If you want the script to fail when the package does, run the package synchronously instead. This will mean that the T-SQL script will run for as long as the package takes to run as well, and the failure will be passed back to the calling statement.

USE SSISDB;
GO

DECLARE @executionID bigint;
EXEC [catalog].[create_execution] @package_name = N'copy_file.dtsx',
                                  @execution_id = @executionID OUTPUT,
                                  @folder_name = N'SSIS_COMMON',
                                  @project_name = N'Common',
                                  @use32bitruntime = False,
                                  @reference_id = NULL;
DECLARE @var0 sql_variant = N'D:\XXXX\XXX\XXX\target\to.xlsx';
EXEC [catalog].[set_execution_parameter_value] @executionID,
                                               @object_type = 30,
                                               @parameter_name = N'target_file',
                                               @parameter_value = @var0;
DECLARE @var1 sql_variant = N'D:\XXXX\XXX\XXX\from_.xlsx';
EXEC [catalog].[set_execution_parameter_value] @executionID,
                                               @object_type = 30,
                                               @parameter_name = N'source_file',
                                               @parameter_value = @var1;

--Change the package to run synchronously 
EXEC [catalog].[set_execution_parameter_value] @executionID,
                                               @object_type = 50,
                                               @parameter_name = N'SYNCHRONIZED',
                                               @parameter_value = 1;

EXEC [catalog].[start_execution] @executionID;

Though you can get log messages using T-SQL, that isn't going to help you here. Your statements to prepare and start the package would occur, and then you would be immediately checking the logs for the status of a package that, likely, has done very little (if anything). You could add a WAITFOR DELAY and wait for x seconds, by why? If the package completes sooner then your T-SQL script is waiting for no good reason, and if the package takes longer than usual to complete you give a false positive (the package hadn't failed when you checked).