Hello,

I have a dts package which its first task is to find out
whether a files exists in a directory.

If the file does not exist, the rest of the dts tasks
will not run, so the DTS package fails.

Is there a task or script that would retry this DTS
package to run again after an hour, until it finds the
text file in the directory.

Any help or hints are very welcome!

thanks!!

You can set a repeating schedule in SQL Agent, and you can check or the
existence of a file in DTS, and if not exit the package. To stop it running
more than once after the file has been found you would have to-

tweak the job schedule from inside the DTS package after the file has been
found. Simplest way to do this would be to set the start date and time for
the schedule to the future, when you expect the next file to arrive. See
sp_update_jobschedule or use SQL-DMO.

or

move the file on the first run to prevent it being found again.


In DTS you can gracefully exit a package if the file is missing-

How can I check if a file exists?
(http://www.sqldts.com/default.aspx?211)


--
Darren Green
http://www.sqldts.com

"martino" <anonymous (AT) discussions (DOT) microsoft.com> wrote

What is SQL-DMO? I'm wanting to call sp_update_jobschedule from a table
trigger to auto-schedule a job. However, I'm generating errors because the
TargetServersRole has been denied execute permissions on the sproc. The
application that will insert data into the table w/the trigger is a CRM web
based, 3 tier application. I need to be able to execute the
sp_update_jobschedule sproc with the trigger. Can I either remove the denied
execute permission on the sp_update_jobschedule sproc or would SQL-DMO help
me in anyway to accomplish this?

"Darren Green" wrote: