Retrigger SSIS package via SQL Agent

Question

I have an SSIS ETL where I need to it to automatically restart on completion as it’s constantly expecting new files to arrive. However, I don’t want an instance continuously running on a loop.

My idea was to use the Execute SQL Agent task before the last package completes but I’ve tested this out and I get an error saying the SQL agent job can’t be started by the same user that has an active job still running.

I’m looking for some advice on the best way to retrigger the SQL agent job. I was thinking of creating a separate job which I would execute to start the desired job - I’m thinking there must be a better way of doing it though.

Any advice or pointers welcome.

Thanks

Answer 1

I put together a post on Polling in SQL Agent This query checks every 5 seconds for a file up to a maximum of 60 seconds (all configurable)

SET NOCOUNT ON;
-- http://www.patrickkeisler.com/2012/11/how-to-use-xpdirtree-to-list-all-files.html
DECLARE
    -- Don't do stupid things like adding spaces into folder names
    @sourceFolder varchar(260) = 'C:\ssisdata\Input'
    -- Have to use SQL matching rules, not DOS/SSIS
,   @fileMask sysname = 'SourceData%.txt'
    -- how long to wait between polling
,   @SleepInSeconds int = 5
    -- Don't exceed 24 hours aka 86400 seconds
,   @MaxTimerDurationInSeconds int = (3600 * 0) + (60 * 1) + 0
    -- parameter for xp_dirtree 0 => top folder only; 1 => subfolders
,   @depth int = 1
    -- parameter for xp_dirtree 0 => directory only; 1 => directory and files
,   @collectFile int = 1
,   @RC bigint = 0;

-- Create a table variable to capture the results of our directory command
DECLARE
    @DirectoryTree table
(
    id int IDENTITY(1, 1)
,   subdirectory nvarchar(512)
,   depth int
,   isFile bit
);

-- Use our sleep in seconds time to generate a delay time string
DECLARE
    @delayTime char(10) = CONVERT(char(10), TIMEFROMPARTS(@SleepInSeconds/60 /60, @SleepInSeconds/60, @SleepInSeconds%60, 0, 0), 108)
,   @stopDateTime datetime2(0) = DATEADD(SECOND, @MaxTimerDurationInSeconds, CURRENT_TIMESTAMP);

-- Force creation of the folder
EXECUTE dbo.xp_create_subdir @sourceFolder;

-- Load the results of our directory
INSERT INTO
    @DirectoryTree
(
    subdirectory
,   depth
,   isFile
)
EXECUTE dbo.xp_dirtree
    @sourceFolder
,   @depth
,   @collectFile;

-- Prime the pump
SELECT
    @RC = COUNT_BIG(1)
FROM
    @DirectoryTree AS DT
WHERE
    DT.isFile = 1
    AND DT.subdirectory LIKE @fileMask;

WHILE @rc = 0 AND @stopDateTime > CURRENT_TIMESTAMP
BEGIN

    -- Load the results of our directory
    INSERT INTO
        @DirectoryTree
    (
        subdirectory
    ,   depth
    ,   isFile
    )
    EXECUTE dbo.xp_dirtree
        @sourceFolder
    ,   @depth
    ,   @collectFile;

    -- Test for file existence
    SELECT
        @RC = COUNT_BIG(1)
    FROM
        @DirectoryTree AS DT
    WHERE
        DT.isFile = 1
        AND DT.subdirectory LIKE @fileMask;

    IF @RC = 0
    BEGIN
        -- Put our process to sleep for a period of time
        WAITFOR DELAY @delayTime;
    END
END

-- at this point, we have either exited due to file found or time expired
IF @RC > 0
BEGIN
    -- Take action when file was found
    PRINT 'Go run SSIS or something';
END
ELSE
BEGIN
    -- Take action for file not delivered in expected timeframe
    PRINT 'Naughty, naughty';
END

To address your specific needs, maybe set the job up like this.

1. Create a job
2. Add to the job server
3. Create the polling job step. On failure, retry in 2 minutes for a max of 30 times (alter this as need be)
4. Create an SSIS running step
5. Define a job schedule that runs every 2 minutes from 6 A.M. to midnight

At this point, we have belts, braces, and suspenders here. The job starts every 2 minutes. In the event the job is already running, nothing bad happens. Once the job begins, it noodles around looking for the file. If it times out, it raises an exception (your TODO) which causes the agent job to wait 2 minutes and then retry up to 30 times.

EXEC msdb.dbo.sp_add_job
    @job_name = N'SQL Agent 2 minutes max'
,   @enabled = 1
,   @notify_level_eventlog = 0
,   @notify_level_email = 2
,   @notify_level_page = 2
,   @delete_level = 0
,   @category_name = N'[Uncategorized (Local)]'
,   @job_id = @jobId OUTPUT;

SELECT
    @jobId;
GO

EXEC msdb.dbo.sp_add_jobserver
    @job_name = N'SQL Agent 2 minutes max'
,   @server_name = N'.\DEV2014';
GO

USE msdb;
GO

EXEC msdb.dbo.sp_add_jobstep
    @job_name = N'SQL Agent 2 minutes max'
,   @step_name = N'Polling'
,   @step_id = 1
,   @cmdexec_success_code = 0
,   @on_success_action = 3
,   @on_fail_action = 2
,   @retry_attempts = 30
,   @retry_interval = 2
,   @os_run_priority = 0
,   @subsystem = N'TSQL'
,   @command = N'SELECT 1 AS PasteInTheFullQuery'
,   @database_name = N'master'
,   @flags = 0;
GO

USE msdb;
GO

EXEC msdb.dbo.sp_add_jobstep
    @job_name = N'SQL Agent 2 minutes max'
,   @step_name = N'Run SSIS'
,   @step_id = 2
,   @cmdexec_success_code = 0
,   @on_success_action = 1
,   @on_fail_action = 2
,   @retry_attempts = 0
,   @retry_interval = 0
,   @os_run_priority = 0
,   @subsystem = N'SSIS'
,   @command = N'/ISSERVER "\"\SSISDB\Demo\ProjectDeploymentModel\Package2.dtsx\"" /SERVER "\".\dev2014\"" /Par "\"$ServerOption::LOGGING_LEVEL(Int16)\"";1 /Par "\"$ServerOption::SYNCHRONIZED(Boolean)\"";True /CALLERINFO SQLAGENT /REPORTING E'
,   @database_name = N'master'
,   @flags = 0;
GO

USE msdb;
GO

EXEC msdb.dbo.sp_update_job
    @job_name = N'SQL Agent 2 minutes max'
,   @enabled = 1
,   @start_step_id = 1
,   @notify_level_eventlog = 0
,   @notify_level_email = 2
,   @notify_level_page = 2
,   @delete_level = 0
,   @description = N''
,   @category_name = N'[Uncategorized (Local)]'
,   @notify_email_operator_name = N''
,   @notify_page_operator_name = N'';
GO

USE msdb;
GO

DECLARE @schedule_id int;

EXEC msdb.dbo.sp_add_jobschedule
    @job_name = N'SQL Agent 2 minutes max'
,   @name = N'Every 2 minutes'
,   @enabled = 1
,   @freq_type = 4
,   @freq_interval = 1
,   @freq_subday_type = 4
,   @freq_subday_interval = 2
,   @freq_relative_interval = 0
,   @freq_recurrence_factor = 1
,   @active_start_date = 20181018
,   @active_end_date = 99991231
,   @active_start_time = 60000
,   @active_end_time = 235959
,   @schedule_id = @schedule_id OUTPUT;

SELECT
    @schedule_id;
GO

Answer 2

Since you don't want the SSIS package to run constantly in a loop, you can schedule the SQL Agent job to run every 10 seconds. That's the most frequently I am able to schedule a job. If the previous run of a job is still going 10 seconds later, then it won't start another instance of the package. Once it completes, it will start it again 10 seconds later.