July 1, 2012 at 11:59 pm
Comments posted to this topic are about the item Using the WMI Event Watcher Task in SSIS to Process Data Files
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 2, 2012 at 12:01 am
Orlando, just 3 words...
Simple. Lucid. Clear.
Raunak J
July 2, 2012 at 3:13 am
This is a nice solution. However when we have hundred other files for similar process it will eventually lock a considerable memory forever.
I was thinking of having split the task into two
1. File watcher task (WMI)
2. File processing task (Package)
The watcher task could be a package or windows service which will be running continuously. It would trigger the processing package when the file is in place.
This way the system resources would be less consumed.
The File watcher task would be singular for all the source files. I.E. it will keep on looping through all the source directories and fire appropriate package as and when the file arrives.
Obviously it would need to have a mapping between file and package stored either in sql or config file.
July 2, 2012 at 5:28 am
opc.three (7/1/2012)
Comments posted to this topic are about the item <A HREF="/articles/SSIS/90571/">Using the WMI Event Watcher Task in SSIS to Process Data Files</A>
I tried this...
but unfortunately , i get a error..
Error: Failed to lock variable "User::WqlquerySource" for read access with error 0xC0010001
"The variable cannot be found.
This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package,
and the variable is not there. The variable name may have changed or the variable is not being created.".
when i validate this expression it works fine, but it gives error when i hit the execute button...
Guys, Could you help me , where am i doing the mistake?
Thanks,
Charmer
July 2, 2012 at 5:34 am
I cleared the previous issue..but again i have come across with the folloeing where i don't have any idea of it...
[WMI Event Watcher Task] Error: Watching for the Wql query caused the following system exception: "Invalid class". Check the query for errors or WMI connection for access rights/permissions.
Could you give me your suggestions friends?
Thanks,
Charmer
July 2, 2012 at 6:30 am
Raunak Jhawar (7/2/2012)
Orlando, just 3 words...Simple. Lucid. Clear.
Thanks for the compliment Raunak, that is exactly what I was going for.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 2, 2012 at 6:35 am
Charmer (7/2/2012)
I cleared the previous issue..but again i have come across with the folloeing where i don't have any idea of it...[WMI Event Watcher Task] Error: Watching for the Wql query caused the following system exception: "Invalid class". Check the query for errors or WMI connection for access rights/permissions.
Could you give me your suggestions friends?
Thanks for trying out the demo. On which OS are you trying this? Just a hunch, if you keyed in the WQL query manually make sure you have two leading underscores in front of the class name:
__InstanceCreationEvent
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 2, 2012 at 6:57 am
Shaun-884394 (7/2/2012)
This is a nice solution. However when we have hundred other files for similar process it will eventually lock a considerable memory forever.I was thinking of having split the task into two
1. File watcher task (WMI)
2. File processing task (Package)
The watcher task could be a package or windows service which will be running continuously. It would trigger the processing package when the file is in place.
This way the system resources would be less consumed.
The File watcher task would be singular for all the source files. I.E. it will keep on looping through all the source directories and fire appropriate package as and when the file arrives.
Obviously it would need to have a mapping between file and package stored either in sql or config file.
Thank you for the kind words Shaun-884394. For discussion purposes, on my machine, the simple package produced by the demo in the article occupied ~15MB of RAM while waiting for a file to arrive. I agree that having one hundred packages loaded concurrently could be a concern depending on the complexity of the packages and how much RAM were available on the server. Although I felt that a scenario involving one hundred files was a bit out of scope for this particular article, it would be interesting to consider for a future article. If that were part of the requirements in a particular environment the ideas you proposed involving a file watching service or a parent/child package approach would definitely be worth exploring during the design phase.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 2, 2012 at 7:29 am
opc.three (7/2/2012)
Charmer (7/2/2012)
I cleared the previous issue..but again i have come across with the folloeing where i don't have any idea of it...[WMI Event Watcher Task] Error: Watching for the Wql query caused the following system exception: "Invalid class". Check the query for errors or WMI connection for access rights/permissions.
Could you give me your suggestions friends?
Thanks for trying out the demo. On which OS are you trying this? Just a hunch, if you keyed in the WQL query manually make sure you have two leading underscores in front of the class name:
__InstanceCreationEvent
Actually, I must say Thanks to you pal.....because this is an awesome article which i have been looking for so many months...you are great ....Thanks a lot...and let me get to my issue... My Os is Window 7.... 2 underscores.....?? Oh i missed that part....Let me try with that and i will let you know opc.three...
Thanks,
Charmer
July 2, 2012 at 7:39 am
Some questions to consider:
1. What kind of Windows Server security permissions does the identity running the SSIS package need in order to execute the WMI query? In a real world environment, I'd assume the file would be dropped to a separate server (maybe a network filer) which is where the WMI connection would need to be pointed, and my SSIS identity is going to be running with the absolute least privileges needed.
2. What is the impact to your SQL server if you have a long-running SSIS package (inside a SQL agent job, presumably) like in your example and you need to either restart the machine or the SQL Agent service? .NET Windows services have events you can handle in your code that allow you to gracefully stop your file watcher. Will the restarts hang when the SSIS package is sitting there? Do you have to manually abort the SQL Agent Job?
3. If a file doesn't get dropped for a couple days, what is the impact to the server to having a SQL agent job running for several days?
July 2, 2012 at 2:48 pm
Well done. Very understandable.
Thanks
-Mike Hayes
July 2, 2012 at 3:37 pm
Thank you for reading the article and for posting these questions.
kpatrick (7/2/2012)
Some questions to consider:1. What kind of Windows Server security permissions does the identity running the SSIS package need in order to execute the WMI query? In a real world environment, I'd assume the file would be dropped to a separate server (maybe a network filer) which is where the WMI connection would need to be pointed, and my SSIS identity is going to be running with the absolute least privileges needed.
By default on Server 2008 R2 the Authenticated Users group has permission to connect to the \root\cimv2\ WMI namespace. You can check the namespace permissions using the WMI MMC snap-in (WmiMgmt.msc located in C:\Windows\System32). As I understand it, once WMI namespace security checks are complete WMI does no further security checks and standard Windows security becomes relevant. The article demo was completed as a local administrator but I just ran some tests as a non privileged user and from a WMI perspective I did not need to configure any special permissions to access the event class in the \root\cimv2\ namespace on localhost. To run the demo package however, because a file is being manipulated in the file system later in the process I did have to make sure the non-privileged account running the SSIS package had the file system permissions necessary to carry out those actions.
In terms of watching files on a network server I know of two options, and there may be more:
1. You can map a drive and refer to the drive letter in your WMI query. I refer to this scenario in Step 1 of the article demo. While mapping a drive may not be practical in a real world scenario for most, for some it may be an option.
2. You can configure your WMI Connection Manager to connect to a remote server. Attaching to an event class on a remote server was out scope for this article however you can see in Step 9 of the demo where I make reference to the option. Have a look at the WMI Connection Manager (SSIS 2012) for details on how to attach to an event class on a remote server. Note that the job runner's permissions on the remote server would need to be configured properly.
2. What is the impact to your SQL server if you have a long-running SSIS package (inside a SQL agent job, presumably) like in your example and you need to either restart the machine or the SQL Agent service? .NET Windows services have events you can handle in your code that allow you to gracefully stop your file watcher. Will the restarts hang when the SSIS package is sitting there? Do you have to manually abort the SQL Agent Job?
3. If a file doesn't get dropped for a couple days, what is the impact to the server to having a SQL agent job running for several days?
Addressing questions 2 and 3:
I do not anticipate any issues with an approach where a job is running at all times the SQL Server Agent service itself is running. There is a long-standing precedence for such a scenario when it comes to SQL Server Replication. Jobs that run the replication Log Reader and Distribution agents may run at all times while supporting continuous transactional replication subscriptions. The replication-job schedules are of type 'Start automatically when SQL Server Agent starts' and the jobs steps never complete, i.e. they are running at all times while the SQL Server Agent service is running.
If it was the intention of the ETL process designer to have a SSIS package watching for files at all times the same could be done in terms of the scheduling the job to start when the SQL Server Agent service started. An auxiliary schedule or schedules would be required so the SQL Server Agent would start the job each day, if it were not still running, prior to the earliest anticipated file delivery time. A nice thing about SQL Server Agent is that it will not start multiple instances of a job, i.e. if a job is running and another scheduled execution time arrives that schedule will be skipped. A schedule secondary to one which starts the job when SQL Servrr Agent starts would account for the operational scenario when the package finds a file, loads it and ends gracefully yet we want that job to again start watching for the file once again.
Regarding stopping a job, or restarting SQL Agent, I tested this and SQL Server Agent has no issues ending a job running a package via DTExec.exe that uses the WMI Event Watcher Task. If your job were configured with a schedule of type 'Start automatically when SQL Server Agent starts' it would begin watching as soon as the service was brought online again.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 2, 2012 at 3:45 pm
mleehayes (7/2/2012)
Well done. Very understandable.Thanks
-Mike Hayes
Thank you, Mike. I am happy you liked it and appreciate the feedback.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 2, 2012 at 7:56 pm
kpatrick (7/2/2012)
Some questions to consider:1. What kind of Windows Server security permissions does the identity running the SSIS package need in order to execute the WMI query? In a real world environment, I'd assume the file would be dropped to a separate server (maybe a network filer) which is where the WMI connection would need to be pointed, and my SSIS identity is going to be running with the absolute least privileges needed.
2. What is the impact to your SQL server if you have a long-running SSIS package (inside a SQL agent job, presumably) like in your example and you need to either restart the machine or the SQL Agent service? .NET Windows services have events you can handle in your code that allow you to gracefully stop your file watcher. Will the restarts hang when the SSIS package is sitting there? Do you have to manually abort the SQL Agent Job?
3. If a file doesn't get dropped for a couple days, what is the impact to the server to having a SQL agent job running for several days?
1. My guess is that it would be running under NT\Autority. If set correctly on the sql server upon installation, then it can read files anywhere on the server easily.
2. Just put a counter in the loop in the script task. If it exceeds a certain threshold, then you can have it exit the loop. If you have to restart job agent, the package will be terminated automatically by sa or the owner of the job.
3. I would instead just schedule the job agent to scan every 30 minutes if your timing is days...that will minimize server stress. You don't want to ever have a package open for days looking for a file.
July 3, 2012 at 3:34 pm
One might event setup a SQL Agent "event" to watch for the file and then kick off the processing job as the response.
Just a thought.
Viewing 15 posts - 1 through 15 (of 50 total)
You must be logged in to reply to this topic. Login to reply