The first two parts of this series addressed the general approach that I use in an SSIS script task to discover and alert on missed SQL Agent jobs. With apologies for the delay in producing this final post in the series, here I bring these approaches together and present the complete package.
To create the SSIS, start with an empty SSIS package and add a data flow task. In the task, add the following transformations.
1. An OLE DB Source. Point it to a connection manager with the instance to be monitored and a SQL command containing this script:
set nocount on declare @xp_results table ( job_id UNIQUEIDENTIFIER NOT NULL, last_run_date INT NOT NULL, last_run_time INT NOT NULL, next_run_date INT NOT NULL, next_run_time INT NOT NULL, next_run_schedule_id INT NOT NULL, requested_to_run INT NOT NULL, request_source INT NOT NULL, request_source_id sysname COLLATE database_default NULL, running INT NOT NULL, current_step INT NOT NULL, current_retry_attempt INT NOT NULL, job_state INT NOT NULL ); /* Values for "job_state": 0 = Not idle or suspended 1 = Executing 2 = Waiting For Thread 3 = Between Retries 4 = Idle 5 = Suspended [6 = WaitingForStepToFinish] 7 = PerformingCompletionActions */ DECLARE @can_see_all_running_jobs INT = 1; DECLARE @job_owner sysname = suser_sname(); INSERT INTO @xp_results EXECUTE master.dbo.xp_sqlagent_enum_jobs @can_see_all_running_jobs, @job_owner; select @@SERVERNAME InstanceName, j.name as job_name, j.date_created, x.last_run_date, x.last_run_time, x.next_run_date, x.next_run_time, x.current_step, x.job_state, x.job_id, s.name schedule_name, s.freq_type, s.freq_interval, s.freq_subday_type, s.freq_subday_interval, s.freq_relative_interval, s.freq_recurrence_factor, s.active_start_date, s.active_end_date, s.active_start_time, s.active_end_time, @@servername ServerName from @xp_results x inner join msdb.dbo.sysjobs j on x.job_id = j.job_id inner join msdb.dbo.sysjobschedules js on js.job_id = j.job_id inner join msdb.dbo.sysschedules s on s.schedule_id = js.schedule_id where j.enabled = 1 and s.enabled = 1 and s.freq_type in (4, 8, 16, 32) and x.job_state not in (1, 2, 3); |
2. Add a sort transformation, with “job_id” as the sort key input column (in ascending order).
3. Add a script component. When prompted, add it as a transformation. On the “Input Columns” tab, select all columns. On the “Inputs and Outputs” tab, select “Output 0” and change SynchronousInputID to “None.”
Expand “Output 0” and select “Output columns.” Add the following as outputs:
- InstanceName, Unicode string, length 256
- JobId, unique identifier
- JobName, Unicode string, length 256
- ExpectedRunDate, database timestamp with precision
- LastRunDate, database timestamp with precision
Return to the “Script” tab and “Edit Script.” At the bottom, find the Input0_ProcessInputRow method and delete it, replacing it with the contents of this ZIP file.
At the top of the script, expand the “Namespaces” section and add these statements:
using System.Collections.Generic; using System.Linq; |
Add the following declarations inside the ScriptMain class, such as immediately before the PreExecute method:
private bool _isFirstRow = true; private RowData _previousRow = null; private DateTime _previousJobRunDate; private List<DateTime> _forecast = null; |
4. Add a conditional split transformation.
In the first row (order = 1), set Output Name = ExpectedRunDateIsNull, Condition = ISNULL(ExpectedRunDate).
In the second row (order = 2), set Output Name = ExpectedRunDateInFuture, Condition = ExpectedRunDate >= DATEADD(“minute”,-30,GETDATE()).
Set the default output name = ExpectedRunDateInPast
5. Add an OLE DB Destination, linking to the ExpectedRunDateInPast output from the condition split. In a database somewhere, create a table to collect the results.
create table MissedScheduleMonitorResults ( ResultId int not null identity(1,1), MonitorRunTime datetime2 not null default (sysdatetime()), InstanceName int not null, JobId uniqueidentifier not null, JobName nvarchar(256) not null, LastRunDate datetime null, ExpectedRunDate datetime null, AlertSent bit not null default (0), constraint pk_MissedScheduleMonitorResults primary key clustered (ResultId), ); |
Create a connection manager to this database, and in the OLD DB destination point to this connection manager and select the MissedScheduleMonitorResults table. Use the default mappings (where column names match).
The data flow task should looks something like this. Yeah, no naming standards here.
That’s it! Now, you can create an job with a script that reads from the table where AlertSent = false, sends an appropriate message, and sets AlertSent to true.