February 6, 2004 at 4:42 am
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
February 14, 2004 at 9:42 pm
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.
Steve.
August 23, 2012 at 10:09 pm
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.
August 24, 2012 at 1:39 am
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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 31, 2012 at 11:47 am
August 31, 2012 at 12:21 pm
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]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply