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.
