SQL Agent Tips: Configure Custom Schedule for SQL Agent Job

There have been few instances of late where I’ve seen requests in the forums asking for scheduling jobs using a custom schedule in SQL Server agent. I had given the solution for them in the forums. Thought of sharing it via this blog for others benefit.
Consider this example scenario. We have a SSIS package which needs to be executed during first day of every quarter of the year. If you see the standard schedules available in SQL agent job, you can see that there is no provision to do this directly as quarter is not one of the options available as per the below screenshot.

In such cases we need to apply an approach as below
Add a new step at the beginning of the job. The purpose of this step would be to check if its a valid day for the job execution. In the above case valid days are quarter start dates.For all the other days we dont need to execute the core steps. The step type would be Transact SQL script and we will use a code as below

IF DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0) <>  DATEADD(qq,DATEDIFF(qq,0,GETDATE()),0)
 RAISERROR (‘Job cant be executed on this day’,16,1) 

What the above code snippet does is to check if current date represents the start date of a quarter. If not, it will throw an error stating that you cant execute job on that day.
The logic for quarter start, current day etc are explained here
http://visakhm.blogspot.in/2010/01/some-quick-tips-for-date-formating.html
Now we configure the job step settings as below for the new step

We configure failure action as quit reporting success for the first step.This will make sure the job will quit reporting success without executing the core steps on the non scheduled days.
Now try executing the job on quarter start and on another day and check the job execution history
The history would look like below for a quarter start day

As you see for quarter start it will execute the core steps. Now change the date to another day and rerun the job and you will see the below in the job execution history
As you see from the above the job reports success with first step showing error that its not valid day for the job run. Also core steps will not get executed at all.
Similarly if you want job to get executed only on Nth occurance of a week day (say 1st Friday) you can tweak the logic like below for the first step
DECLARE @dt datetime = GETDATE()

IF dbo.GetWeekDayNumberOfMonth(@dt) = 1 AND DATEDIFF(dd,0,@dt) % 7 = 4
 SELECT 1
ELSE
  RAISERROR (‘Job cant be executed on this day’,16,1) 


The UDF GetWeekDayNumberOfMonth can be found in the below link
And the logic to find out week day number can be found here
This approach can be used to execute a sql agent job as per the custom schedule defined.  Just change the logic used in the first step as per your requirement and it will make sure the job gets executed only on the schedule you want.

Published by dataplatformcentral