Timer
-
Hi,
Do you have any idea how I could implement a timer inside a dts package ?
What actually i want is to perform a serie of directory look up and process the files within this folder , but whenever there is no files, i want the package to sleep for 30 sec and restart to check if there is any new file to process .
Any idea or suggestion would certainly help.
J.
-
Can you use WAITFOR? Check BOL for more details.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Any way of doing it with VBSCRIPt inside of an ActiveX Task?
J.
-
Why not just use the SQL Scheduler (or any other suitable package) to schedule 1 pass of the DTS package every minute?
Don't complicate things....use what tools are there already.
-
Well I did that for a previous job, but each time the processing time was above 60 sec, the job scheduler launch the same job again before the end of the first one which result to an annoying error notification.
Another thing is that the input folder is filled constantly but on an irregular basis.
J.
-
If a job is 'running' then the job scheduler will NOT start the next scheduled iteration of that job.
-
On Completion of your step that works with the files, create an Execute SQL task with
WAITFOR DELAY '000:00:30'.
On Completion, create an AxtiveX with vbScript of
Dim oPKG
Dim oConnection
Set oPKG = DTSGlobalVariables.Parent
oPKG.Steps("DTSStep_DTSActiveScriptTask_1").ExecutionStatus = DTSStepExecStat_Waiting
Main = DTSTaskExecResult_Success
DTSStep_DTSActiveScriptTask_1 should be replaced with the name of the first step
That will restart the first step.
This is a very trimmed downed version of what I use, so you may need to refine this a bit before it'll work properly, but you get the idea.
Dan
-
That is what I wanted,
Many thanx,
-
No problem.
The only thing I think needs to be said (you're probably aware of this) is that you need to keep in mind that the files may be in the middle of copying when you go to process them. Be sure to have a mechanism in place to trap for that possibility.
C'ya
-
Yep i am aware for this , but to be honest i haven't faced this situation during the test phases.
Also , The script has a mechanism for re processing the uncomplete files which is i think sufficient for such tasks.
J.
-
"Be sure to have a mechanism in place to trap for that possibility."
a DOS rename of FILEA to FILEA, testing for a 0 retcode will prove that a file is not 'open'.
-
great solution, another method I have used for a long time is to check for the existence of a dummy text file, if it exists it means the DTS is currently running, so exit the DTS, if not create the dummy file and continue.
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
Dim oFSO, sFileName
Set oFSO = CreateObject("Scripting.FileSystemObject")
sFileName = "c:\DTSLocked.txt"
If oFSO.FileExists(sFileName) then
Main = DTSTaskExecResult_Failure
Else
Set txtFile = oFSO.CreateTextFile("c:\DTSLocked.txt", true, false)
txtFile.WriteLine("Interface is running. DO NOT DELETE THIS FILE")
txtFile.Close
Main = DTSTaskExecResult_Success
END IF
Set txtFile = Nothing
Set oFSO = Nothing
End Function
And just add a delete file activex script as your last step.
I have found this very useful for scheduled dts packages that are triggered over very short time periods but the actual process might take longer than the scheduled time.
-
Thank you Both,
The timer is working perfectly.
J.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply