0

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

1 Answer 1

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.

1
  • I'll go ahead and try this. Thanks!
    – user45867
    Jun 24, 2019 at 21:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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