Poll For a File

Question

Poll For a File

Jimmy White

SSC Journeyman

Points: 88
More actions
February 6, 2004 at 4:42 am

#160420

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.
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 // This ends the loop
Else
        wait(5) //This is where I want to sleep
        //Go to start and check for file again
End if
LOOP
End Function
=========================
I know I could write a loop function but Im afraid that it will be too hard
on the CPU!!
Any Ideas greatly appreciated

Thanks in advance
February 9, 2004 at 8:00 am

This was removed by the editor as SPAM

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 1

If you interested I can send you a copy of a windows service that I've just finished writing. I wrote it because we've come across similar issues, and I hate polling - I like a push strategy more than a pull strategy.

Basically it uses the FileSYstemWatcher (class in .net) to monitor a specified directory/file, and then sends a message to a MSMQ message queue when the file is created (or delted, or renamed etc etc). So the DTS package now has as it's first step a MSMQ task that is waiting on a message indicating that the file is now there.

I can send more info (and the app) if you're interested. Basic requirements are .net framework 1.1 and MSMQ on the machine. Am currently using only local message queues, so no active driectory is required.

Steve.

n79799 SSC Eights! Points: 804 More actions · Answer 2

Can be done in a variety of ways.....using the Message Queue like suggested by another member.

I would suggest something simpler....

1) Create a dtsx package(say checkFile.dtsx) having a simple script task just checking for a file(no sleepingof threads.....no waiting for 5 mins etc)....just check with FileExists method. If the file does not exists.....force fail the package by failing the script. If the file is found return success.

2) Now create another ssis(say processFile.dtsx) which assumes the file is there. and just do the normal of the processing of the file(read, transform, load etc).

3)Create a job in sql server agent. Configure the first step to run checkFile.dtsx. Also configure it to try say 10 times at intervals of 5mins each(this can be done in the advanced tab of the step).

Configure the second step to run processFile.dtsx.

This way the first step wouldn't go to the next step until it succeeds.....or if even after 10 tries, the file is not there, the first step would fail and would not run the second step which runs processfile.dtsx.

Hope this helps.

Phil Parkin SSC Guru Points: 246962 More actions · Answer 3

I've used the File Watcher task in the past for situations like this. See here.

In summary, you execute your package with the FW task as the first task in the package. The FW task will complete only when the target file becomes available.

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

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 4

A couple options:

Using the Konesans File Watcher Task in SSIS to Process Data Files[/url]

Using the WMI Event Watcher Task in SSIS to Process Data Files[/url]

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

Note: Eight year old thread.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]