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