November 20, 2008 at 11:46 am
I have 2 large ETL loads that need to run on the first Sunday of every month. The first load must precede the second. This part is pretty straight forward.
I have a weekly load that needs to run by itself, excluding the first Sunday of every month. The problem is that some months have 5 Sundays and others have only 4.
Does anyone know how to schedule a job to run every Sunday, excluding the first Sunday of the month without having the job execute twice on the months that only have 4 Sundays. The options in the monthly schedule are first, second, third, fourth, and last.
- Kerry
November 20, 2008 at 12:21 pm
I would code the logic into the ETL Package (I am assuming SSIS). It is pretty easy to specify not to run if the current day of the month is <= 7 using a precedence constraint.
In a package where you want everything to be skipped, add a sequence container and put everything in the package into this new container. Then, add an EMPTY sequence container to the package. Drag the precedence constraint from the empty sequence container to the one with all of your components. Then, just code the logic into the expression for the precedence constraint.
The empty sequence container will run first (doing nothing) and everything in the second container will only run if the constraint is met.
Then, you can just run the weekly package every sunday and it will figure out it should not run in the first week.
November 20, 2008 at 2:31 pm
how about one mentioned just below, is this what you mean:-
IF DATENAME ( dw , getdate() ) = 'Sunday' AND datepart(dd,getdate()) > 7
BEGIN
---- your script
END
November 20, 2008 at 2:47 pm
If you are using SQL agent you can choose first Sunday of every month choosing monthly and first Sunday of the month. If you need other Sunday create a monthly schedule for them.
If you put one package after another agent will ensure one does not start until the other is complete and can not run the second if the first fails.
November 24, 2008 at 9:07 am
Kerry,
Interesting question and I had to try it to see what would happen. If you schedule the job to run monthly on the second, third, fourth, and last Sunday, it will run only once on the fourth Sunday if there are only four in the month.
To test it, I scheduled a job to run on the fourth and last Monday so I could see how many times it would run today. The job ran only once, which implies that if two schedules conflict, only one will execute the job.
So you can get what you want by creating 4 monthly schedules for the job.
Greg
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply