SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Using the Konesans File Watcher Task in SSIS to Process Data Files


Using the Konesans File Watcher Task in SSIS to Process Data Files

Author
Message
Orlando Colamatteo
Orlando Colamatteo
SSC Guru
SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)

Group: General Forum Members
Points: 78914 Visits: 14499
Comments posted to this topic are about the item Using the Konesans File Watcher Task in SSIS to Process Data Files

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Scott D. Jacobson
Scott D. Jacobson
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2498 Visits: 1020
This looks neat but I have 1 question. Please pardon my ignorance as I've never used a 3rd party Task object in SSIS. Say I develop a package using this on my machine or in a test environment. When I go to deploy to a production server, that machine must also have this installed correct? I would think so but I could also see that once the package is built, maybe the code is already in the package and the component itself is not necessary?

If I had to rephrase, is the component merely required to build the package or is it also required for execution?
dcdanoland
dcdanoland
SSC Veteran
SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)

Group: General Forum Members
Points: 263 Visits: 166
Thanks for the article. I've been intrigued by this component, but have shied away from it because I have qualms about the architecture. I worry about having an SSIS job run continuously. I just don't think SSIS was designed to run that way. I doubt that Microsoft tests this capability. What happens if an error occurs and the package ends prematurely? I think the package would have to be manually restarted. As much as I like doing things in SSIS, I think we'll end up writing a Windows service in .NET because it will give us more control in a more standardized architecture. Unfortunately, writing a service will be a lot more work. I guess I could be persuaded to use the FileWatcher task within SSIS, but I would need real-world results and a thorough discussion on the architectural pros and cons of using the task.
James Stephens
James Stephens
SSC Veteran
SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)

Group: General Forum Members
Points: 201 Visits: 210
I agree the Konesans may be easier to implement, but I just finished a rebuild of a set of ETL tasks in which I gutted Konesans and went to WMI.

Not sure if it was a version issue, but Konesans introduced file-locking issues, and a several other 'non-specific' odditites, errors, and instability that started as soon as my team had implemented the Konesans tool into the package. All issuses disappeared now that WMI is used. Coincidence? Maybe. But I've got heavy production and I'm running smooth now and not sweating it out and getting phone calls at all hours of the evening with complaints that the job didn't load data.

This is just one anecdotal bit of evidence, but I'm interested to see of others have come across strange locking and resource issues with Konesans.
--Jim
Orlando Colamatteo
Orlando Colamatteo
SSC Guru
SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)

Group: General Forum Members
Points: 78914 Visits: 14499
Scott D. Jacobson (8/13/2012)
This looks neat but I have 1 question. Please pardon my ignorance as I've never used a 3rd party Task object in SSIS. Say I develop a package using this on my machine or in a test environment. When I go to deploy to a production server, that machine must also have this installed correct? I would think so but I could also see that once the package is built, maybe the code is already in the package and the component itself is not necessary?

If I had to rephrase, is the component merely required to build the package or is it also required for execution?

Unfortunately the code required to implement the File Watcher Task functionality is not embedded into the SSIS package itself when it is built and deployed. Only a reference and the metadata required to instruct the external component is added to the SSIS package. You must run the File Watcher Task installer on all development workstations and servers where you want to run a package that leverages the component. This is a downfall to using thrid-party SSIS components, and one of the major barriers for adoption in my opinion (there are others, closed-source being the second one that comes to mind in the case of the File Watcher Task).

In my previous article I showed how to use the WMI Event Watcher Task included in SSIS to accomplish a similar result. The Konesans File Watcher Task offers some advantages to using the WMI Event Watcher when in comes to ease of use, but it has some disadvantages too. Thanks for having a look at the article.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Orlando Colamatteo
Orlando Colamatteo
SSC Guru
SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)

Group: General Forum Members
Points: 78914 Visits: 14499
dcdanoland (8/13/2012)
Thanks for the article. I've been intrigued by this component, but have shied away from it because I have qualms about the architecture. I worry about having an SSIS job run continuously. I just don't think SSIS was designed to run that way. I doubt that Microsoft tests this capability.

I must respectfully disagree with your assertion. I can only offer you indirect evidence since I do not work for Microsoft but the example provided in the WMI Event Watcher Task documentation implies they expect an SSIS package might be run continuously:

From WMI Event Watcher Task (SQL Server 2012):

The following query watches for notification that the CPU use is more than 40 percent.

SELECT * from __InstanceModificationEvent WITHIN 2 WHERE TargetInstance ISA 'Win32_Processor' and TargetInstance.LoadPercentage > 40

The following query watches for notification that a file has been added to a folder.

SELECT * FROM __InstanceCreationEvent WITHIN 10 WHERE TargetInstance ISA "CIM_DirectoryContainsFile" and TargetInstance.GroupComponent= "Win32_Directory.Name=\"c:\\\\WMIFileWatcher\""


dcdanoland (8/13/2012)
What happens if an error occurs and the package ends prematurely? I think the package would have to be manually restarted.

If the package ends prematurely then you would need to start the package again. Ideally a package will never end prematurely, however if it does it either means the exception handling needs improvement or manual intervention is intentionally provoked, or in some cases both.

dcdanoland (8/13/2012)
As much as I like doing things in SSIS, I think we'll end up writing a Windows service in .NET because it will give us more control in a more standardized architecture. Unfortunately, writing a service will be a lot more work. I guess I could be persuaded to use the FileWatcher task within SSIS, but I would need real-world results and a thorough discussion on the architectural pros and cons of using the task.

I am of the opinion that there is nothing inherently wrong with running SSIS packages continuously. The example I showed above taken from the Microsoft documentation ought to be sufficient to show that it was one intended use of SSIS, however feel free to disagree.

If I break it down SSIS is nothing more than a set of XML instructions (open a dtsx file in Notepad). The runtime (DTExec.exe) simply interprets those instructions. So, when I think about running SSIS continuously I'm really thinking about running an instance of DTExec.exe continuously and DTExec.exe is an executable just like any other executable. It is subject to memory leaks, runaway threads, unhandled exceptions, race conditions, etc. however I am not too concerned about it given the level of effort Microsoft has put into developing and testing it. Couple that with the number of users in the community helping to solidify its usage patterns and report bugs. I am aware of some memory leak issues in SSIS 2005 with the Foreach Loop Container as well as the Execute Package Task but those were quickly corrected. There are many people using those tasks (and the other built-in tasks) therefore the problem surfaced quickly and a fix was produced by Microsoft.

In the case of the Konesans File Watcher Task, while it is not produced by Microsoft its central functionality is built around a class in the .NET Framework, FileSystemWatcher Class, which brings it into the same domain as other built-in SSIS tasks in terms of being backed by Microsoft.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Orlando Colamatteo
Orlando Colamatteo
SSC Guru
SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)

Group: General Forum Members
Points: 78914 Visits: 14499
James Stephens (8/13/2012)
I agree the Konesans may be easier to implement, but I just finished a rebuild of a set of ETL tasks in which I gutted Konesans and went to WMI.

Not sure if it was a version issue, but Konesans introduced file-locking issues, and a several other 'non-specific' odditites, errors, and instability that started as soon as my team had implemented the Konesans tool into the package. All issuses disappeared now that WMI is used. Coincidence? Maybe. But I've got heavy production and I'm running smooth now and not sweating it out and getting phone calls at all hours of the evening with complaints that the job didn't load data.

This is just one anecdotal bit of evidence, but I'm interested to see of others have come across strange locking and resource issues with Konesans.
--Jim

I personally have not had file locking issues with Konesans. It may depend on which events your predecessors keyed off of when using the Konesans File Watcher Task. At any rate it sounds like you have reached a more stable solution going straight to WMI.

My previous article Using the WMI Event Watcher Task in SSIS to Process Data Files may be of more interest to you. The Konesans File Watcher Task and the WMI Event Watcher Task implement similar functionality, but differently internally and under certain stressors unique to each production environment one may be more suitable than the other. Thanks for sharing your experience and for having a look at the article(s).

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
dcdanoland
dcdanoland
SSC Veteran
SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)

Group: General Forum Members
Points: 263 Visits: 166
You make very good points regarding Microsoft testing and the WMI Event Watcher Task. I guess my qualm is that it seems to be an afterthough by Microsoft. All the other tasks are designed to not run continuously, and SSIS's architecture seems designed for such tasks. It doesn't seem as if SQL's architecture is really designed for continuously running tasks. Otherwise they would provide more support for that would be somewhat like SQL Agent.
Orlando Colamatteo
Orlando Colamatteo
SSC Guru
SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)

Group: General Forum Members
Points: 78914 Visits: 14499
dcdanoland (8/13/2012)
You make very good points regarding Microsoft testing and the WMI Event Watcher Task. I guess my qualm is that it seems to be an afterthough by Microsoft. All the other tasks are designed to not run continuously, and SSIS's architecture seems designed for such tasks. It doesn't seem as if SQL's architecture is really designed for continuously running tasks. Otherwise they would provide more support for that would be somewhat like SQL Agent.

I agree, it can seem that way. Running packages continuously is typically not the first usage pattern that comes to mind when designing a process. In my experience the goto for batch processing and ETL design is usually some variation of:

1. start a package that checks for the existence of a file
2a. if the file is there load it and exit
2b. if the file is not there exit

Then a SQL Agent job will be setup to run this process once per minute, or once every few minutes, during a time period when the file is expected to arrive. The pattern is intuitive, is tried and true, and does work fine in most cases but adds a lot of transient overhead to the server in terms of CPU, memory allocations/deallocations as well as msdb activity as is detailed in the article. Some doubts about this same topic were (hopefully) laid to rest in the comments of my WMI Event Watcher Task article as well if you have a moment to read through those.

The precedence for running an Agent job continuously was set long ago as well, by Microsoft. In the SQL Server Replication subsystem the Log Reader and Distribution Agents are always running per a SQL Agent job that is created when replication is configured. Again, it may not be intuitive to have a job that runs an SSIS package continuously but in my opinion there is nothing inherently wrong with the design pattern and in some cases it may be the most suitable option.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
dcdanoland
dcdanoland
SSC Veteran
SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)

Group: General Forum Members
Points: 263 Visits: 166
Thanks for referencing the discussion on the WMI article. It was most helpful. Your description of the standard batch processing and ETL design matches my experience.

What sort of load have you used on the WMI file watcher package? We typically have about 300-1,000 files in an 8 hour window.

Multi-threading always makes me nervous, particularly in an SSIS script task. Granted, all you do is "Thread.Sleep(5000)", but does that allow other tasks and processes to take over and possibly inflict some undesirable side-effects? In short, is it thread-safe?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search