How to schedule a job run on 6th working day of every month?

dataservices administration
1

Hi, i am using Data Services XI 3.0. I have a job need to run monthly, which is on 6th working day of every month. Example: the 6th working for June 2009 is on 8th June, exclude Saturday and Sunday.

Is there anyway to set this type of schedule in Data Services scheduler? Or i need to create a table (which day is belongs to which working day) in SQL server, and pass the variable equal 6 to the scheduler?

Please advise, thanks!

ylai20 :malaysia: (BOB member since 2008-05-21)

2

DS just uses the native OS scheduler’s underlying capabilities. Since (on Windows) the Task Scheduler service can’t do a business-days calc, neither can DS/DI.

Creating a table of dates, for the next 10 years, a column of which includes the business-day-in-month, might work. You’d schedule the job to wake up and run every day, but it would include a Conditional that checks to see if today is one of the valid business days. If not, the rest of the job wouldn’t run.

Or get a third-party scheduling tool…

dnewton :us: (BOB member since 2004-01-30)

3

Thanks!
Please correct me if i am wrong for designing the flow.
WF_Check_Working_Day → Query (condition working day=6, then continue)–> WF_Run_All_Job

If condition is false, then it wont continue to run the WF_Run_All_Job…

Is this the way you mean?

ylai20 :malaysia: (BOB member since 2008-05-21)

4

Better to have a such kind of conditional statements in batch script or shell script before the job execution starts, not inside the dataflow. That’s what “dnewton” mentioned I believe.

gssharma :india: (BOB member since 2006-10-30)

5

Actually I was thinking of something like:

  • Script step is the first thing in a job, evaluates $WorkDayOfMonth by selecting it from a DATE table using SQL() function.

  • Conditional has a $WorkDayOfMonth=6 condition in it. If true, it runs a workflow that contains the job. If false, it does nothing.

If you restricted yourself solely to a batch script, you’d have to have code in that script to figure out the WorkDayOfMonth, and then you’re using some other toolset…

dnewton :us: (BOB member since 2004-01-30)

6

Gotcha… That’s a valid conditional statement :yesnod:

gssharma :india: (BOB member since 2006-10-30)

7

Perhaps the following biterscript ( http://www.biterscripting.com ) can help. In the batch file, use the gettime() system function. It returns current time in yyyymmddhhMMss format. If the dd is “06”, that would be our day. I wrote a brief batch script for you.

var str day
set $day = gettime() ; chex "6[" $day > null ; chex "4]" $day > null
while ($day <> "06")
do
    sleep (60*60*24)
    set $day = gettime() ; chex "6[" $day > null ; chex "4]" $day > null
done
# It is now the 6th of the month. Do something.
system "something"

The chex cmmand is character extractor.

Jenni

JenniC (BOB member since 2009-06-10)

8

Note, however, the requirement is for the 6th business day of the month, which might not be the 6th calendar day of the month.

dnewton :us: (BOB member since 2004-01-30)

9

Hi,

I remember we had this requirement in one of my project, to ran all the jobs on 5th business days of every month.

This is what I did:

I wrote a custom function in DI, which takes date as a input and return business days as a number.

And there is another table , which maintains list of Holidays for XX number of years.

All you have to do then, have a counter in our script, assigned it to one, check for date which you passed to the function, increment the counter only if the date passed is not a weekend (sat and sun) and/or not a holiday.

Since, I have to run this Job on every 5th business days, I scheduled it to run through 5th to 11th of every month. Every Day it takes that date, and returns appropriate business day.

So in the Conditional Workflow something like:

if business_Days (sysdate()) = 5) then execute the job, if not just print something!

It worked really great!

Hope you get the idea!

Happy Scripting!

data_guy :us: (BOB member since 2006-08-19)