Poll For a File

Hi All,

I am using ActiveX code in a DTS package (Package1) to search a directory for a file.
When the file is there I want to start a DTS package (Package2) to load
in this file.

I will schedule Package1 to run at 10pm, however since the external file is being generated externally I cannot guarentee that the file will be there at 10pm. Therefore I want to Poll the folder and not start package2 until I have the file. I am using the If file exists method.

How do I poll for a file, and if its not there wait for 5 mins and poll again until I find the file.

Thanks in advanceHave you tried FSO objects?

Dim objFSO
Set objFSO = CreateObject("Scripting.FileSystemObject")|||I have tried the FSO and thats what I am using.

My problem is that if the file is not there, I want to wait for a period of
time ie 5 mins and then try again. And keep trying every five mins until I get the file.

Some thing like this:

========================
Function Main()

dim result

set objFSO = CreateObject("Scripting.FileSystemObject")

result = 1

DO WHILE result = 1
If objFSO.FileExists("c:\test.txt") Then
//Start other Package
result = 99
Else
wait(5)
//Go to start and check for file again
End if

LOOP

End Function
=========================

So in essence the above code will LOOP until it finds the file.
I just need something to replace the wait(5)

Any Ideas

Thanks in advance

I have wait(5) but wait is not a valid function|||EXEC [master].[dbo].[xp_fileexist] 'c:\new_auth.dat1'

This might help|||Originally posted by superquinn
I have tried the FSO and thats what I am using.

My problem is that if the file is not there, I want to wait for a period of
time ie 5 mins and then try again. And keep trying every five mins until I get the file.

Some thing like this:

========================
Function Main()

dim result

set objFSO = CreateObject("Scripting.FileSystemObject")

result = 1

DO WHILE result = 1
If objFSO.FileExists("c:\test.txt") Then
//Start other Package
result = 99
Else
wait(5)
//Go to start and check for file again
End if

LOOP

End Function
=========================

So in essence the above code will LOOP until it finds the file.
I just need something to replace the wait(5)

Any Ideas

Thanks in advance

I have wait(5) but wait is not a valid function

Why do not create a new function (I did not check it, sorry) and call it:

Function wait(theDate)
Do Until DateDiff("n", Now, theDate)<5
Loop
End Function|||I dont want to write a loop function like you have suggested as this constant looping might be very hard on the CPU. I was hoping there was a clever way or else an in built wait/sleep function available to me in the ActiveX component of the DTS.

Can Anyone help?|||You could write a Agent Job that will run every 5 minutes from 10 to 12. Sounds like the easiest way.|||Hi,

Im not quiet sure I understand what you mean by an agent Job.

I am only new to SQL Server and DTS.

The polling for a file is causing a lot of trouble for me.
Can someone please help|||In enterprise Manager, expand Management, SQL Server Agent, then click on Jobs. From there you can add a new job, which can execute ActiveX scripts, T-SQL scripts, even DTS packages. You can add one or many schedules to kick off the job.

I think it may be what you are looking for.

You can get started by right-clicking on the DTS package you created, and selecting Schedule Package, which will set the DTS package up to run as a job. You can then adjust the schedule to your liking, add more steps, etc.|||Would the command WAITFOR DELAY be useful?

Here is the help from MS SQL Server Books Online...
WAITFOR
Specifies a time, time interval, or event that triggers the execution of a statement block, stored procedure, or transaction.

Syntax
WAITFOR { DELAY 'time' | TIME 'time' }

Arguments
DELAY

Instructs Microsoft SQL Server to wait until the specified amount of time has passed, up to a maximum of 24 hours.

'time'

Is the amount of time to wait. time can be specified in one of the acceptable formats for datetime data, or it can be specified as a local variable. Dates cannot be specified; therefore, the date portion of the datetime value is not allowed.

TIME

Instructs SQL Server to wait until the specified time.

Remarks
After executing the WAITFOR statement, you cannot use your connection to SQL Server until the time or event that you specified occurs.

To see the active and waiting processes, use sp_who.

Examples
A. Use WAITFOR TIME
This example executes the stored procedure update_all_stats at 10:20 P.M.

BEGIN
WAITFOR TIME '22:20'
EXECUTE update_all_stats
END

For more information about using this procedure to update all statistics for a database, see the examples in UPDATE STATISTICS.

B. Use WAITFOR DELAY
This example shows how a local variable can be used with the WAITFOR DELAY option. A stored procedure is created to wait for a variable amount of time and then returns information to the user as to the number of hours, minutes, and seconds that have elapsed.

CREATE PROCEDURE time_delay @.@.DELAYLENGTH char(9)
AS
DECLARE @.@.RETURNINFO varchar(255)
BEGIN
WAITFOR DELAY @.@.DELAYLENGTH
SELECT @.@.RETURNINFO = 'A total time of ' +
SUBSTRING(@.@.DELAYLENGTH, 1, 3) +
' hours, ' +
SUBSTRING(@.@.DELAYLENGTH, 5, 2) +
' minutes, and ' +
SUBSTRING(@.@.DELAYLENGTH, 8, 2) +
' seconds, ' +
'has elapsed! Your time is up.'
PRINT @.@.RETURNINFO
END
GO
-- This next statement executes the time_delay procedure.
EXEC time_delay '000:00:10'
GO

Here is the result set:

A total time of 000 hours, 00 minutes, and 10 seconds, has elapsed! Your time is up.|||Hi,

Thanks for your reply. I dont think I am explaining myself fully

I know how to sechdule the Package.
I know how to check if file exists

What I need to know is how to halt processing in a activex script
for a period of time.

If the file is not there initially, I want the wait a while and the reloop to
the start of the package and so on until the file is there

==========ACTIVEX SCRIPT========

Set foundfile = 0

Do While foundfile = 0

if foundfile = 1 then
set foundfile = 1 (This breaks loop)
//Carry on processing
Set Success
else
//Halt procesing for 5 mins and then continue
//This is where I need HELP!!

end if

loop|||I think we understand what you are asking, but are offering solutions that differ from the model you are chasing.

For instance, when I mentioned a scheduled job, I meant that you could create a task that would check for the file's existence. If were not there, the job would schedule itself to run again in a few minutes, then exit. If it were there, proceed to the next step, which would kick off your DTS package.|||The problem with having an ActiveX script wait, is that you are tying up a thread and processor resources just counting away clock cycles. The SQL Agent dooes that already, so you might as well take advantage of it.|||Originally posted by bpdWork
I think we understand what you are asking, but are offering solutions that differ from the model you are chasing.

For instance, when I mentioned a scheduled job, I meant that you could create a task that would check for the file's existence. If were not there, the job would schedule itself to run again in a few minutes, then exit. If it were there, proceed to the next step, which would kick off your DTS package.

=====================================
This sounds EXACTLY like what I want to do. Im sorry about this
but Im new to SQL Server.

How do you get a Job to reschedule itself in code?
I assume this is done in ActiveX|||Off the top of my head, I would say that you would need to use an ActiveX script to access the SQLDMO object, and control it from there. I have done such a thing before. I will see what I can find in my old code heap.|||I think I can help

You should create your package wiht the activex script ect...
Then schedule it to run every 5 mins. as a SQL job.

The active x script should look like this. No need to make the activex script wait, just have it run again and again. If no file, it just quits, when it finds one it runs the other import package.

Function Main()

Dim objFSO
Dim objFolder
Dim objFile
Dim oPKG
Dim fileName
Dim folderName

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder("\\server\share$\dir\")

For Each objFile in objFolder.Files

fileName = objFile.Path

Set oPKG = CreateObject("DTS.Package")
oPKG.LoadFromSQLServer "Server", "sa", "", , , , , "2nd package name here"
oPKG.GlobalVariables("fileName").Value = fileName
oPKG.Execute
oPKG.Uninitialize()
Set oPKG = Nothing

objFSO.MoveFile fileName, folderName 'move file somewhere
Next

Main = DTSTaskExecResult_Success

End Function

Hope this helps

Steve|||yes thats the easiest way,

The first step in your package will be a activeX task with the following script inside

==========================================
Function Main()
Dim fs, MyFile
Dim FileLocation
Dim FileName
Dim FileInfo

FileName = "file.txt"

FileLocation = DTSGlobalVariables("Invoice_Input_Location").Value

FileInfo = FileLocation & FileName

Set fs = CreateObject("Scripting.FileSystemObject")

If (fs.FileExists(FileInfo)) Then
fs.MoveFile (FileInfo),(FileLocation & "new_file.txt")
set fs=nothing
Main = DTSTaskExecResult_Success
exit function
Else
Main = DTSTaskExecResult_Failure
exit function
End If

End Function
==========================================

rest of the steps inside the DTS will be after success of this task. The package will be schedules to run after every 5 mins, if your file is present, it will be renamed with a different name (so that next time when the package runs afetr 5 mins it does not process it all over again), this renamed file can be used for further transformations. This way the package will run successfully just once everyday and will fail for rest of the times. which is what you want.