Script to wait for file to start executing package. Event based Triggering.
-
Hi:
I would like my DTS package to wait for a trigger file to show up on the SQL Server machine to start executing. I would like to schedule the package to start at say 7 AM. The fisrt task in the package will look for a file called "done.txt" in C:\triggerfiles\ folder. IF the file exists then the step will execute and delete the file. If file does not exist then the step will keep looping and waiting for the file.
I was wondering if anyone has written a code which would do this?
Thanks for your help.
-
Write a simple Active X script using Scripting.FileSystemObject.
-
Hi,
you can also use this simple T-SQL statement:
declare @result int
exec @result = xp_cmdshell 'del d:\path\done.txt'
while (@result = 1) begin
waitfor delay '00:01:00'
exec @result = xp_cmdshell 'del d:\path\done.txt'
end
You can set as wait time whatever you like.
Matthias
-
Matthias :
Your solution certainly works.
Others have mentioned that :
1. I can use "retry functionlality built in the job" - I was wondering if you can point me where this feature is located in the tool.
2. I can "write ActiveX script" - I was looking for some sample code.
-
Place the following code in an activeX task as the first task in the package:
Function Main()
Set objFSO = CreateObject("Scripting.FileSystemObject")
If objFSO.FileExists("c:\done.txt") then
Main = DTSTaskExecResult_Success
Else
Main = DTSTaskExecResult_Failure
End If
Set objFSO=nothing
End Function
Next set up a job. An easy way to create the job is to right click on the package and click schedule.
Click on the steps tab, then double click on a step (or click edit). Then click advanced tab.
Set retry attempts and interval as appropriate. For example, I have a job that runs a package that looks for a file and is scheduled to run at 2. I set retry attempts at 4 and retry interval to 15. So the job starts at 2, if the file isn't there it tries again at 2:15, etc. until 3, when it fails if the file still doesn't exist. You could also just loop within the activex task, but the job with retries gives you an easy way to stop it at a certain time and alert you via page or email if the package has not been succesful.
Bill
-
To set the Retry in the job.
Edit the step, select the Advanced tab, fill in values for 'Retry attempts' and 'Retry Interval'.
--------------------
Colt 45 - the original point and click interface
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply