May 30, 2013 at 4:57 am
Hi,
Am trying to schedule an ssis package for once a month is it possible to tell the scheduler, if it is a sunday then it should run next day?
Thanks in Advance.
May 30, 2013 at 5:39 am
If you want it to run on a particular day number each month, but move to the next day only on a Sunday, the built in scheduler won't be able to do that.
You'd need to schedule the job daily and wrap the actual job execution around a custom statement (e.g. in T-SQL) that checked the day of month and day of week and only executed on the given day (or day after if the given day was a Sunday). E.g.:
--populate with getdate() in a job step - fixed date used here in order to test
DECLARE @date DATE= '20130601' ,
@PreferredDayofMonth INT= 1
IF ( ( DATENAME(weekday, @date) != 'Sunday'
AND DATEPART(DAY, @date) = @PreferredDayofMonth
)
OR ( DATENAME(weekday, @date) = 'Monday'
AND DATEPART(DAY, @date) = @PreferredDayofMonth + 1
)
)
BEGIN
PRINT 'code to execute job goes here'
END
ELSE
BEGIN
PRINT 'Do Nothing'
END
Alternatively, you can set the scheduler to always run on (for e.g.) the first Monday of the month, but that means the actual day of month that it executes won't be fixed.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply