Rescheduling a package

[WhiZa]

I have a package I want to run daily. This package is dependent on whether or not a file is available. Basicly what I want to do is schedule the package to run every day, and if the file isn't there then to reschedule the package to run in 1 more hour.
Any ideas?

 

[JayKusch]

Hmmm... one way to handle this task would be to set your job, that was created by scheduling the package, to run once every hour of the day.

Now create a table with a DateTime field maybe called HasCompleted.

In your DTS Package, first thing you would do is create a SQL Task that checks to see if HasCompleted is set to todays date meaning it has already ran. The code may look like ...

SELECT HasCompleted
FROM MyTable
WHERE CONVERT(Char,HasCompleted,101) = CONVERT(Char,GetDate(),101)

IF @@RowCount <> 0 -- Means it has ran
... EXIT
ELSE
... Proceses the file

When Processing the file and when the file DOES FINALLY EXIST and is processed successfully we need to set the HasCompleted field to todays date so that the next hour(s) it runs, HasCompleted will be set to todays date and will pass thru the package without processing any further. The code may look like this ...

UPDATE MyTable
SET HasCompleted = GetDate()(),101)


Thanks

J. Kusch