For loop vs File watcher vs WMI Event watcher in SSIS 2005

Question

Post reply

For loop vs File watcher vs WMI Event watcher in SSIS 2005

hamsa_apj

SSC Eights!

Points: 825
More actions
July 5, 2012 at 7:03 am

#263707

Hi All,
I would like to write SSIS package that will process a data file with different names as soon as it arrives in a specific directory?
While googling i found that there are different ways of implemetion like For Loop, File Watcher task , WMI Event watcher task, etc..
Could any one please clarify the difference among them and suggestions to use the better one..
If possible please compare and contrast the performance among them .
Thanks in Advance.

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

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

Phil Parkin SSC Guru Points: 246979 More actions · Answer 1

Which version of SSIS are you planning to use?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

hamsa_apj SSC Eights! Points: 825 More actions · Answer 2

Currently we are using SSIS 2005 and currently share your thoughts on 2008 as well. Please help me to process the SSIS data files in best way.

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 3

hamsa_apj (7/5/2012)
I would like to ...{snip}... process a data file with different names as soon as it arrives in a specific directory?

I have found that most such requirements are overstated. Are you sure that you need to do this or will a scheduled job that runs every so often (say, once evey ten minutes to an hour) actually be sufficient?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

hamsa_apj SSC Eights! Points: 825 More actions · Answer 4

Yes its correct. Job triggerrs the SSIS package and it is scheduled to run every 5 min. For loop task is used in the package to iterate the .dat files in the directory and process the same.

My question is how far the file watcher task and WMI Event task differs from this "For - loop" , in both functional and performance perspective.

Please clarify

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 5

hamsa_apj (7/6/2012)
Yes its correct. Job triggerrs the SSIS package and it is scheduled to run every 5 min. For loop task is used in the package to iterate the .dat files in the directory and process the same.
My question is how far the file watcher task and WMI Event task differs from this "For - loop" , in both functional and performance perspective.
Please clarify

Both tasks will monitor the folder and fire off if a file arrives. So it's instant. However, you need to check if the package finishes after those tasks found a file. If it does, you still need to restart the package.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

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

This article explores, compares and contrats some of the issues being discussed here: Using the WMI Event Watcher Task in SSIS to Process Data Files [/url]. The article demo is done using SSIS 2012 however the WMI Event Watcher Task is available in SSIS 2005 so the content will still be relevant for you.

I have another article coming out in August that implements the same requirements using the Konesans File Watcher Task and while it too uses SSIS 2012 for the demo the same is true with respect to it being relevant on SSIS 2005.

Usually a job scheduled to run once every few minutes will suffice, however there are downsides and proprties of that approach that may require us to look into processing files as soon as they arrive.

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