| Author |
Topic |
|
SHardy
Starting Member
35 Posts |
 Posted - 2006-08-24 : 05:27:31
|
| Hi,I have a DTS that I need to run on a monthly basis. However, I'm not sure how I would go about scheduling it.The DTS needs to run each month after all monthend GL postings have been made. This has been defined as the "3rd working day of each month". Unfortunately this isn't exactly the same as using the following schedule settings:MonthlyThe 3rd Weekday of every 1 month(s).The "weekday" differs to "working day" because it does not take account of bank holidays (public holidays/ non-working days).So for example:January 2007 starts on a Monday. However, this is a public holiday. Therefore I would want the DTS to run on Thursday 4th January 2007, but the scheduler settings above would cause it to run on Wednesday 3rd January 2007.I would be very grateful for any suggestions of how I would acheive the required schedule results.Many thanks,Simon |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2006-08-24 : 07:09:11
|
| There are some pretty smart people in this place so I'm positive that someone will have a better solution than mine, but here's my contribution.I would probably create a table and manually type in each date when the dts should be run by going through my calendar. And type in dates for at least a few years. Then schedule the dts to run daily and as a first step check if the current date matches any of the dates in your table, and if you find a match run it, if not just exit. There would be some overhead in running this every day but a single lookup to your dates-table isn't all that much and it should be quite easy to set up. I have no clue whatsoever on how to do all this automatically...--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-08-24 : 07:47:42
|
| >> The DTS needs to run each month after all monthend GL postings have been made.Is that always the 3rd working day of each month?Defining a working day is also a minefield.You could be getting into the realms of building a corporate scheduler.Simplest for this requirement is to schedule the process to run evey day but the stored procedure that is run checks to see if this is the 3rd working day of the month and only runs the dts package if it is.Part of the process should be to log that the package has been run for that month and this checked so that it is not run again for that month.For this you will need to maintain a table of dates that are not weekends or working days.Usually this sort of thing is triggered by someone signing off the posting - usually the head of the department.If you have a lot more requirements like this then you may need to implement something likehttp://www.nigelrivett.net/Products/Scheduler.html==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
SHardy
Starting Member
35 Posts |
Posted - 2006-08-24 : 11:09:05
|
| OK. So if I create a table with the run schedule, how would I use this to trigger if I run the DTS? Would this be done via a stored procedure? Or would I start the DTS with an ActiveX task to test the date & fail/success acordingly?BTW, I have very limited knowledge when it comes to either stored procedures or ActiveX. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-08-24 : 11:18:08
|
| Easiest to control it via a stored procedure then you can run the package via xp_cmdshell.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-08-24 : 11:18:38
|
It may be easier to create a job that checks the schedule, and if is a day that the DTS job should run, starts the DTS job with procedure msdb.dbo.sp_start_job-- Start jobexec msdb.dbo.sp_start_job 'My DTS Job' CODO ERGO SUM |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-08-24 : 11:22:50
|
| You could do that but that'll just execute a dtsexec - might as well do it directly from the sp unless there is some other reason not to, like being able to run the package from a job independently or a permissions issue.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-08-24 : 11:34:22
|
| I often find it convenient to do it this way if:It is a multi-step job.orI would like to be able to see the history of the actual run in the job history.CODO ERGO SUM |
|
|
|
|
Scheduling a DTS - Not quite so straight forward!