SSIS job -- how to create stored procedure that repeats SSIS jobs?

Question

Hey I realize I can probably create a modular parent package that repeats SSIS jobs in Visual Studio itself .... but I know you can execute SSIS packages using a stored procedure in SSMS -- 'exec ssisdb.catalog.create_execution @package_name=N'myPackageName' ..... etc

For a quick and dirty way to execute an SSIS package like this say ... 'X' number of times ...

Is there way to do this? Like ... on package end, repeat ... for X times?

Or even simpler, if the job duration is known/ predictable ... "execute task, wait 5 minutes .... execute again ... for X times?"

Answer 1

This may be a little less elegant than you are looking for but I would imagine this would work ok for you.

CREATE PROCEDURE dbo.ExecuteSSISPackageOnLoopWithWaitTimer
(
    @PackageName CHAR(100), 
    @NumberOfLoops INT, 
    @WaitTimer VarChar(20) = '00:05' --five minutes
)
AS
BEGIN
    DECLARE @InstanceCount INT = 0
    IF(@NumberOfLoops < 0 OR @NumberOfLoops IS NULL)
    BEGIN
        SET @NumberOfLoops = 0
    END

    WHILE (@InstanceCount < @NumberOfLoops)
    BEGIN
        exec ssisdb.catalog.create_execution @package_name=@PackageName

        IF(@WaitTimer IS NOT NULL AND @WaitTimer <> '')
        BEGIN
            WAITFOR DELAY @WaitTimer
        END

        SET @InstanceCount = @InstanceCount + 1
    END
END

Taken from a previous Stack Overflow question about SQL Wait Timers

WAITFOR DELAY '02:00' --Two hours
WAITFOR DELAY '00:02' --Two minutes
WAITFOR DELAY '00:00:02' --Two seconds
WAITFOR DELAY '00:00:00.200' --Two tenths of a seconds

Hopefully that give you what you need.