how to delay and loop until file arrives

brettbrimhall · December 9th, 2004, 03:25 PM

I have a situation where a file is sent every hour and overwrites the old file. The time it arrives is variable, usually between 15 and 30 minutes after the hour. However, there are some times it doesn't arrive until 50 minutes past the hour or not even at all.

My objective is to import this data within 2 minutes of arrival, whenever that is, and then reschedule for the next hour. If the file hasnât arrived by 50 minutes after the hour I want to send a notification to myself and the file sender that it has not arrived and then reschedule to try again the next hour.

What I havenât been able to figure out is how to implement a 2 minute delay in a VBScript task and loop around to the beginning of the package? Does anyone have any ideas?

jemacc · December 9th, 2004, 03:44 PM

Using Active X Scripts
1.You may want to first check if the file exists

Code:

'**********************************************************************
'  Visual Basic ActiveX Script
'************************************************************************

Function Main()
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.GetFile("<path to your file or server>")

If  objFile.Size >0 Then
main = DTSTaskExecResult_success
else
main = DTSTaskExecResult_failure
end if
End Function

2.On failure end task
3.On success continue with next task
4. Schedule your DTS Package accordingly.

Jaime E. Maccou

brettbrimhall · December 9th, 2004, 03:54 PM

Thanks for the response.

If I exit the package and reschedule how can I set up a time out feature when the files doesn't arrive that notifies me?

Or if I stay in the package is there a way to implement a delay before looping that doesn't consume resources?

jemacc · December 9th, 2004, 05:22 PM

schedule the package as a job and set the time and date intervals

Jaime E. Maccou

jemacc · December 9th, 2004, 05:26 PM

Set up another activex script to email you on failure

Code:

'**********************************************************************
'  Visual Basic ActiveX Script
'************************************************************************

Function Main()
Dim iMsg 
 
set iMsg = CreateObject("CDO.Message")
 
Dim objMail    
Set objMail = CreateObject("CDO.Message")
 
objMail.From = "<your sqlagent email address>"
objMail.To ="<your email address>"
objMail.AddAttachment ("")
objMail.Subject="subject"
objMail.TextBody = "body"
objMail.Send
Set objMail = nothing
 
Main = DTSTaskExecResultSuccess
End Function

Jaime E. Maccou

joefawcett · December 13th, 2004, 11:11 AM

Alternatively keep an eye on the folder in question:
Using .Net

http://www.dotnetcoders.com/web/Arti...spx?article=28

Using WMI and Script

http://groups-beta.google.com/group/...3965ed1e916570

--

Joe (Microsoft MVP - XML)