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?"


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
    DECLARE @InstanceCount INT = 0
    IF(@NumberOfLoops < 0 OR @NumberOfLoops IS NULL)
        SET @NumberOfLoops = 0

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

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

        SET @InstanceCount = @InstanceCount + 1

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.

| improve this answer | |
  • I'll go ahead and try this. Thanks! – user45867 Jun 24 '19 at 21:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.