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
Enjoy Reading This Article?
Here are some more articles you might like to read next: