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


Using the WMI Event Watcher Task in SSIS to Process Data Files


Using the WMI Event Watcher Task in SSIS to Process Data Files

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

Group: General Forum Members
Points: 77952 Visits: 14499
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
Raunak Jhawar
Raunak Jhawar
SSCertifiable
SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)

Group: General Forum Members
Points: 6467 Visits: 1944
Orlando, just 3 words...

Simple. Lucid. Clear.

Regards/Raunak
Now a member of Linkedin

Please visit the all new Performance Point Forum
Please visit the all new Data Mining and Business Analytics Forum
Shaun-884394
Shaun-884394
SSC-Addicted
SSC-Addicted (426 reputation)SSC-Addicted (426 reputation)SSC-Addicted (426 reputation)SSC-Addicted (426 reputation)SSC-Addicted (426 reputation)SSC-Addicted (426 reputation)SSC-Addicted (426 reputation)SSC-Addicted (426 reputation)

Group: General Forum Members
Points: 426 Visits: 1121
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.
Charmer
Charmer
SSCertifiable
SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)

Group: General Forum Members
Points: 5942 Visits: 1081
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
Charmer
Charmer
SSCertifiable
SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)

Group: General Forum Members
Points: 5942 Visits: 1081
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
Orlando Colamatteo
Orlando Colamatteo
SSC Guru
SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)

Group: General Forum Members
Points: 77952 Visits: 14499
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
Orlando Colamatteo
Orlando Colamatteo
SSC Guru
SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)

Group: General Forum Members
Points: 77952 Visits: 14499
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
Orlando Colamatteo
Orlando Colamatteo
SSC Guru
SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)

Group: General Forum Members
Points: 77952 Visits: 14499
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
Charmer
Charmer
SSCertifiable
SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)

Group: General Forum Members
Points: 5942 Visits: 1081
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
kpatrick
kpatrick
SSC Journeyman
SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)

Group: General Forum Members
Points: 84 Visits: 89
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?
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