| Author |
Topic |
|
chilluk
Starting Member
24 Posts |
 Posted - 2002-06-05 : 10:24:34
|
| Is there a way that I can configure a timeout for a DTS Package or a SQL Agent Job, so that if it's been running for more than X minutes it will stop. I have a SQL Agent Job running some SQL stored procedures, and a DTS package - a timout on the job as a whole would be good, but if only on the DTS package then that will do?Thanks.Chris Hillchris@softwarefirst.com |
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-06-05 : 10:55:11
|
| Some of the individual steps have timeouts, like the Execute Process task, but there is no overall timeout setting. Why do you want one? And are you running your DTS packages on SQL 7 (i feel for you if so) or 2K?setBasedIsTheTruepath<O> |
 |
|
|
chilluk
Starting Member
24 Posts |
Posted - 2002-06-05 : 11:00:52
|
| I am using SQL 2000.The reason I wanted a timeout, is because sometimes the DTS fails for some reason (usually because the files it is trying to pick up have not been FTP'ed properly due to line failure or similar) so the DTS often trys and trys to suck in the files and fails. When it does this it often makes the server very slow and as it's behind a website this is not good. I just want to be able to say if it hasn't run in say 1 hour, then stop trying.Thanks.Chris Hillchris@softwarefirst.com |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-06-05 : 11:03:55
|
| Gotchya.If you're polling a directory for files to be FTP'd, then I imagine you're using a ActiveX scripting task to do so. You'd have to place the timeout logic there.setBasedIsTheTruepath<O> |
|
|
|
chilluk
Starting Member
24 Posts |
Posted - 2002-06-05 : 11:07:00
|
| I'm not polling - the files are uploaded to a schedule, and so the DTS jobs euns on a similar (but later schedule). We allow about 10 times the amount of time needed to upload the files for the FTP to run, but on occasions as described something goes wrong with the line to put a spanner in the works.Chris Hillchris@softwarefirst.com |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2002-06-05 : 11:12:40
|
Why not have an ActiveX task to check if the files are there as the first step of the DTS package? Thats assuming the files are always of the same name, which I guess they are if you are using DTS to drag them in....  PeaceRick |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-06-05 : 11:16:51
|
quote:
I'm not polling ...
That's the ultimate cause of the problem. You should implement a loop w/ a fixed # of iterations looking for the file and waiting some amount of time if it's not there.setBasedIsTheTruepath<O> |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-06-05 : 11:33:14
|
| to add one more straw to the camel's back....you not only have to wait for the file to arrive in your directory, but you have to wait for the copying of the file into that directory to complete ... cause you can't dts a file in that's not finished copying<O> |
|
|
|
chilluk
Starting Member
24 Posts |
Posted - 2002-06-05 : 11:56:08
|
| How do I do that? Checking for the file is no issue - but it will vary in size etc as the data changes all the time...Chris Hillchris@softwarefirst.com |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-06-05 : 12:09:33
|
 bwahaha. . . no actually, it won't very in size (as reported by the file attributes) . . . I try attempt to rename the file and catch an error . . . on success, I name it back to its original name. If you can find a better solution, please share . . .<O> |
|
|
|
|
DTS / SQL Agent timeout