How to conditionally schedule an SSIS package?

Question

How to conditionally schedule an SSIS package?

tan110

Hall of Fame

Points: 3855
More actions
August 3, 2009 at 9:51 am

#137098

I have a monthly job that I want to run every 1st of the month. But if the 1st of the month falls on either a Sunday or Monday than I don’t want to kick off the job till until Tuesday. Is there any way to conditionally schedule a this as a job?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

edwardelliott SSCommitted Points: 1863 More actions · Answer 1

There must be quite a few different ways to do it, you could schedule it and have a script task in ssis decide whether to run - or every year you could create 12 one-off schedules for the days you want it to run. I am sure there will be more ways...

ed

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 2

Try creating e new job with following as its first step

if (DATEPART(dd, GETDATE()))=1 and (DATEPART(weekday, GETDATE()))>2

begin

Exec msdb..sp_start_job 'Call the job you need to execute here'

end

HTH!

MJ

Libby Ten Centuries Points: 1175 More actions · Answer 3

Hi MANU-J.

This is exactly the same thing I'm trying to accomplish. So if I'm understanding correctly what you're suggesting is to create another job that checks the date condition and if the result is true, then run another job.

I'm wondering about the main secondary job. Would I just set it up without a schedule? Actually, it needs to run every Sunday during the month after quarter-end...so only 4 months each year. The issue I have is when there are 5 Sundays. There are only options to run the 1st - 4th Sunday. I can also schedule to run the "last" Sunday - but if there are only 4, then it would run twice; once for the 4th and a second time for the "last". So could I schedule the secondary job to run the 4 Sunday's and then set this new job up to execute the job on the 5th Sunday when applicable?

Thanks for your help on this.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

MANU-J. (8/6/2009)
Try creating e new job with following as its first step
if (DATEPART(dd, GETDATE()))=1 and (DATEPART(weekday, GETDATE()))>2
begin
Exec msdb..sp_start_job 'Call the job you need to execute here'
end
HTH!
MJ

MJ, you need to add in a little more sophistication - something like this, in pseudocode:

IF (Today is Tuesday 2nd or 3rd)

OR (Today is Monday 2nd)

OR (Today is Tuesday, Wednesday, Thursday, Friday or Saturday 1st)

Run job

John

Edit - added in Monday 2nd bit. There's several ways of arranging the logic - above is only one of them.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

Libby (10/16/2013)
I'm wondering about the main secondary job. Would I just set it up without a schedule?

Yes.

Actually, it needs to run every Sunday during the month after quarter-end...so only 4 months each year. The issue I have is when there are 5 Sundays. There are only options to run the 1st - 4th Sunday. I can also schedule to run the "last" Sunday - but if there are only 4, then it would run twice; once for the 4th and a second time for the "last". So could I schedule the secondary job to run the 4 Sunday's and then set this new job up to execute the job on the 5th Sunday when applicable?

I think the easiest way is to run the job every Sunday, and do a test for the month number only.

John

Libby Ten Centuries Points: 1175 More actions · Answer 6

Libby

Ten Centuries

Points: 1175

October 16, 2013 at 9:38 am

#1658983

Thanks John! I will try that.