Executing an SSIS package from Stored Procedure

I recently needed to do this for a report. In theory, the SSIS package was going to run on some sort of schedule, but then it was decided that the user wanted the ability to run the report on-demand. Since the SSIS package didn’t take too long to execute, I decided I would try to run it on-demand. I was using this in an SSRS report, so I had to build a stored procedure to do this.

The key here is that I had to run the SSIS package, wait until the package was done, and then return data. I also didn’t want the package to potentially run indefinitely, and so if a package ran longer than the timeout or the job ran into an issue, the report should return empty.

NOTE: Make sure the user that will run the procedure also has access to run the SSIS package. (This is granted by giving user access to SSIS database; more here)

This is what I came up with which ended up working rather nicely:

-- =============================================
-- Author:		Pablo Aizpiri
-- Create date: 7.15.2015
-- Description:	Runs the SSIS package that returns results
-- results
-- =============================================
ALTER PROCEDURE [dbo].[RefreshAndQueryFromSSIS]
	@TIMEOUT_SECONDS INT = 60 -- Max number of seconds to wait for SP to execute.
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	DECLARE @EXECUTION_ID BIGINT, @OUTPUT_EXECUTION_ID BIGINT

	-- Setup SSIS package to run:
	EXEC SSISDB.CATALOG.CREATE_EXECUTION
						@FOLDER_NAME = '[SSIS Package Folder Name]'
						,@PROJECT_NAME = '[SSIS Project Name'
						,@PACKAGE_NAME = '[SSIS Package .DTSX File Name]'
						,@EXECUTION_ID = @EXECUTION_ID OUTPUT

	-- Run SSIS package:
	EXEC SSISDB.CATALOG.START_EXECUTION @EXECUTION_ID
	SET @OUTPUT_EXECUTION_ID = @EXECUTION_ID;

	DECLARE @STATEID AS INT = 0, @SECONDSRAN AS INT = 0
	-- Waits until package is successfully completed or until max timeout is reached:
	WHILE (@STATEID < 3 OR @STATEID = 5) AND @SECONDSRAN < @TIMEOUT_SECONDS
	BEGIN
		WAITFOR DELAY '00:00:00:5'; -- Give time back to CPU while waiting for timeout
		SELECT @STATEID = status, @SECONDSRAN = DATEDIFF(SECOND, start_time, GETUTCDATE())
		FROM SSISDB.catalog.executions
		WHERE execution_id = @EXECUTION_ID;
	END

	SELECT *
	FROM [Results table here]
	WHERE @STATEID = 7 -- this ensures results are only returned if the package execution was successful.
END