Requirement:
I have a SQL Server Agent Job load process that needs to wait for a control file to be updated prior to executing the consecutive steps. The file arrives some time after 5AM - but as late as 7AM.
Solution:
In a DTS package, I have an ActiveX script that checks the file's age. If it is less than 4 hours, the package succeeds. Otherwise the package fails:
-----------------------------------------------------
Function Main()
Dim fso, f1
Set fso = CreateObject("Scripting.FileSystemObject")
Set f1 = fso.GetFile(\\myshare\mycontrolfile.TXT) 'put your file name here
'If less than 4 hours, return success
If DateDiff( "h" , f1.DateLastModified, Now ) < 4 Then
Set fso = nothing
Set f1 = nothing
Main = DTSTaskExecResult_Success
Else
Set fso = nothing
Set f1 = nothing
Main = DTSTaskExecResult_Failure
End If
End Function
----------------------------------------------------------------
The SQL Server Agent Job calls this DTS package starting at 5AM. This job step is the FIRST job step in the data load job. If the step succeeds, the next steps will run. If it fails, I have the job step configured to retry the step in 15 minutes, for 8 attempts (from 5AM to 7AM). You can configure retry attempts on the Advanced tab of the specific job step.