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