Executing SSIS Packages in SSIS Catalog

We can execute SSIS packages deployed in SSIS Catalog using stored procedure based approach as given below:

DECLARE @execution_id INT

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

@package_name = N'Package.dtsx',

@execution_id = @execution_id OUTPUT,

@folder_name = N'FolderName',

@project_name = N'ProjectName',

@use32bitruntime = False;

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

@execution_id,

@object_type = 30,

@parameter_name = N'FolderName',

@parameter_value = N'ParameterValue';

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

@execution_id,

@object_type = 30,

@parameter_name = N'EnvironmentPath',

@parameter_value = N'EnvironmentValue';

exec catalog.set_execution_parameter_value  @execution_id, 50, 'DUMP_ON_ERROR',1

exec catalog.set_execution_parameter_value  @execution_id, 50, 'LOGGING_LEVEL',3

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

Once we execute the package, we can check the status of execution using the below approach:

DECLARE @status AS BIGINT = 1;

WHILE(@status = 1 OR @status = 2 OR @status = 5 OR @status= 8)

BEGIN

PRINT @status

PRINT 'waiting 5 seconds for Package to finish'

WAITFOR DELAY '00:00:5';

SET @status = (SELECT [Status] FROM SSISDB.[catalog].[executions]

        WHERE execution_id = @execution_id);

END

If we want to execute the SSIS package using commandline, we can take the below approach:

dtexec.exe /isserver "\SSISDB\FolderName\ProjectName\Package.dtsx" /server "ServerName" /parameter $Package::FolderName;"ParameterValue" /parameter $Package::Sleep(Int32);60 /parameter $Package::MaxRun(Int32);300 /parameter $Package::EnvironmentPath;"EnvironmentValue"