Programaticaly executing packages in SSIS 2012 project deployment model

The usual way to execute SSIS packages programmatically was by invoking Dtexec utility until SQL 2012. With the arrival of project deployment model in SSIS 2012 we can now rely upon catalog view and system stored procedures for executing SSIS packages deployed to SSISCatalog relational DB. This post discusses the approach that can be followed for executing package deployed in project model  programmatically and then checking the execution status for setting correct execution sequence thereafter.

The scenario was like this. There was a data feed coming from third party which had to be first uploaded to a table in the database. Then based on the outcome of this data load there were a series of data loads which included some lookup logic to the first table and based on that do some data loading into the warehouse tables. The initial data load was not automatic but had to be manually driven by mean of upload screen.

The approach followed is given below.
It consists of creating an execution for the main package and then starting it. Once started, a logic was written to monitor the execution result. Once the result is returned ie package execution finished, result is checked whether it was success. If yes, the execution will proceed with rest of the steps otherwise it will stop the procedure raising an error.
The stub for the solution is given below
The package should be deployed to SSISDB catalog as shown below

Then you can use code as given below



DECLARE @exec_id BIGINT

    EXEC [SSISDB].[catalog].[create_execution] 
        @package_name=N'Package.dtsx',     --SSIS package name TABLE:(SELECT * FROM [SSISDB].internal.packages)
        @folder_name=N'MyTestDeploy', --Folder were the package lives TABLE:(SELECT * FROM [SSISDB].internal.folders)
        @project_name=N'Integration Services Project2',--Project name were SSIS package lives TABLE:(SELECT * FROM [SSISDB].internal.projects)
        @use32bitruntime=TRUE, 
        @reference_id=NULL,             --Environment reference, if null then no environment configuration is applied.
        @execution_id=@exec_id OUTPUT   --The paramter is outputed and contains the execution_id of your SSIS execution context.

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

SELECT @exec_id


DECLARE @exec_status int,@end_time datetimeoffset

WHILE @end_time IS NULL
BEGIN
SELECT @end_time = end_time,@exec_status=status
FROM [SSISDB].internal.execution_info
WHERE execution_id = @exec_id
IF @end_time IS NOT NULL
BEGIN
 IF @exec_status != 7
PRINT ' Package Failure'
ELSE 
PRINT 'Success'
         -- Other tasks to follow

    END
WAITFOR DELAY '00:00:15'

SELECT 'Checking loop'
END

This logic can be used for checking the execution status and then determining the sequence of operation for the rest of the steps.