Sql-server – SSIS job — how to create stored procedure that repeats SSIS jobs

sql serverssisstored-procedures

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

Best Answer

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.

Related Question