Can you dynamically re-schedule a DTS job?
-
I have a DTS package that runs and does multiple things, but the first step is to pick up a file from another server, so the first step is an FTP task. I read an article earlier today checking for a new file - I think I can get it to determine the date of the file as the file I pick up each day is the same filename...only the date is different. The problem is that it processes whichever file it picks up (even if the prior day file
).Is there a way that I can DYANMIALLY reschedule the job if the new file is not there yet? It would be helpful to say add an hour to check if the new file is ready, but I still want to run the DTS package at the same time each day. (This is if I can get the other to work, assuming that I.
) Typically the new file is ready at 5am and I pick it up at 5:30. If the host hasn't processed it yet (sometimes it delays at quarter-end) I want to check again at 6:30 for the new file, but want to continuously run at 5:30. OK...did I confuse you??Thanks!!
Joanie
-
You could use sp_update_jobschedule to add an hour and exit if there was no new file, then after a successfull download reset the schedule to 5:30.
-
Do you really need to it be 1 hour, or is that just an example, because if it's only a 5-15 minutes you could add an execute SQL task where you can execute a WAITFOR command for however much time you need. Once that is completed it goes back to your FTP task and starts again. You could even set it to go every 5 minutes or every minute so that as soon as the new file is there it executes.
That way you don't need to Monkey with the Job Schedule and it stays where it currently is scheduled. And if your process takes more than an hour extra you have some built in redundancy so that it will execute with no interaction from yourself.
Just in case it might ever go into an infinite loop because something went wrong you can set a global variable to use as a counter and if the counter reaches a certain point Exit the job with failure and have it mail you so you can go and fix it.
-
Awesome! Thanks! This should give me enough to make it happen. There are definitely benefits to both approaches.
-
I was originally going to suggest a WAITFOR DELAY '01:00:00' step in the package, but the amount of scripting you have to do in DTS to get it to go through a loop is much more complicated than a single call to reschedule the job.
WAITFOR has the drawback of holding a connection open and tying up a thread scheduler the whole time it waits. On a busy server this can be an issue, although maybe not at 5:30 in the morning. I have used it before and wouldn't hesitate to use it again for some things, but I'm not sure I would put it in a regularly scheduled job if there was an easy alternative.
-
Yes, the reason we run our DTS packages that early is because of server traffic. This is changing from a weekly run to a daily run - their file won't be ready until as late as 9am now so we are thinking of having a one day delay because of server load/traffic. If we move it to a night run (9pm) then we would be able to manage it a little more seamlessly with WAITFOR. This gives me some options to test. Thanks for the replies!
-
What I would do is have a short VBScript as the first step of the SQL Agent Job that checks for the file. Then just set the appropriate amount of retries for that step.
--------------------
Colt 45 - the original point and click interface
-
The below code can be used to re-schedule the job through code:
EXEC msdb.dbo.sp_update_jobschedule @job_name = 'NameofJob', @name = 'NameofJob',@freq_subday_interval=2
The other parameters also can be checked
Sanjay Masawan
-
You could also add some activex code to:
1. check for a file - if found, continue, otherwise exit successfully
2. At the end of the package or after processing the file, move it/rename it.
Set your schedule to run more often and it will process within a certain time of the file being available.
-Pete
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply