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


WMI task and WQL query


WMI task and WQL query

Author
Message
Ian C0ckcroft
Ian C0ckcroft
SSC Eights!
SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)

Group: General Forum Members
Points: 960 Visits: 863
Hi guys, I am using a WMI task to watch for files. We get about 20 txt files. when they all downloaded loaded we get a trigger file that tells us to start processing.
the trigger files will have the date in the file name e.g 20120801_Trigger.txt

my WQL query id:
SELECT * FROM __InstanceCreationEvent WITHIN 10
WHERE TargetInstance ISA 'CIM_DataFile' AND TargetInstance.Name = 't:\\SAP_SandD\\trigger.txt'


at the moment the WMI task runs but just stays yellow. ANy idea why?
also, how do I put a wild card mask it the filename so ny trigger file with different dates is picked up?

once it pcks up a file, an event handler is triggered to run the package.

thanks a mil
Ian

Ian Cockcroft
MCITP BI Specialist
Orlando Colamatteo
Orlando Colamatteo
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14983 Visits: 14396
You can use LIKE with WMI. I would recommend modifying your query to use TargetInstance.Path, TargetInstance.FileName and TargetInstance.Extension instead of TargetInstance.Name for performance reasons. Using .Name as you have it is like doing a recursive directory listing from T:\ looking for a file you already know the path to, and every 10 seconds.

Try something like the WMI query used in this article.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Ian C0ckcroft
Ian C0ckcroft
SSC Eights!
SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)

Group: General Forum Members
Points: 960 Visits: 863
Thanks opc,
that is the article I used to dev with and then I tried all different options.
i ORIGINALLY USED
SELECT *
FROM __InstanceCreationEvent WITHIN 1800
WHERE TargetInstance ISA 'CIM_DataFile'
AND TargetInstance.Drive = 'T:'
AND TargetInstance.Path = '\\Business Intelligence\\SAP Stock and Distribution File Dump\\'
AND TargetInstance.FileName LIKE '%Trigger%'
AND TargetInstance.Extension = 'txt'

BUT GOT AN ERROR
[WMI Event Watcher Task] Error: An error occurred with the following error message: "Invalid parameter ".

any ideas?

Ian Cockcroft
MCITP BI Specialist
Orlando Colamatteo
Orlando Colamatteo
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14983 Visits: 14396
Ian C0ckcroft (8/2/2012)
Thanks opc,
that is the article I used to dev with and then I tried all different options.
i ORIGINALLY USED
SELECT *
FROM __InstanceCreationEvent WITHIN 1800
WHERE TargetInstance ISA 'CIM_DataFile'
AND TargetInstance.Drive = 'T:'
AND TargetInstance.Path = '\\Business Intelligence\\SAP Stock and Distribution File Dump\\'
AND TargetInstance.FileName LIKE '%Trigger%'
AND TargetInstance.Extension = 'txt'

BUT GOT AN ERROR
[WMI Event Watcher Task] Error: An error occurred with the following error message: "Invalid parameter ".

any ideas?

Hmm, that looks good to me. Here is the exact WMI used in the article and it has the same structure as what you have shown.

SELECT * 
FROM __InstanceCreationEvent WITHIN 5
WHERE TargetInstance ISA 'CIM_DataFile'
AND TargetInstance.Drive = 'C:'
AND TargetInstance.Path = '\\@\\ExcelDrop\\'
AND TargetInstance.FileName LIKE 'BusinessData%'
AND TargetInstance.Extension = 'xlsx'


Which OS and which version of SSIS are you using? I think the WMI used in my article will work on any version of SSIS and any OS equal to or newer than XP but it was only tested in SSIS 2012 on Windows 7.

I vaguely remember running into the Invalid Parameter error when developing the package and it was related to the variable mapping. Double-check the variable value is properly mapped into the WMI Task property.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
eclispe66
eclispe66
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 24
Was this ever resolved? I am trying to do something similar and received the same error message. I am not sure what I did wrong.

WqlQuerySource = "SELECT * FROM __InstanceCreationEvent WITHIN 5 WHERE TargetInstance ISA 'CIM_DataFile' AND TargetInstance.Drive = 'H:' AND TargetInstance.Path = '"+ REPLACE( @[User:Tongueath], "\\", "\\\\") +"' AND TargetInstance.FileName LIKE '"+ @[User::FileNamePrefix] +"%' AND TargetInstance.Extension = '"+ @[User::FileExtension] +"'"
Orlando Colamatteo
Orlando Colamatteo
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14983 Visits: 14396
Which OS and SSIS version are you using? Did you try the demo as-is to ensure your systems can handle the technique before you made modifications for your environment?

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Ian C0ckcroft
Ian C0ckcroft
SSC Eights!
SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)

Group: General Forum Members
Points: 960 Visits: 863
Eclipse, I cant remember. But I did get it to work. I been off site for a few months so cant even remember what I did .

Ian Cockcroft
MCITP BI Specialist
eclispe66
eclispe66
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 24
opc.three (10/22/2012)
Which OS and SSIS version are you using? Did you try the demo as-is to ensure your systems can handle the technique before you made modifications for your environment?


I am using Windows 7 64-bit with SSIS 2008.
Orlando Colamatteo
Orlando Colamatteo
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14983 Visits: 14396
What about my second question?

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
eclispe66
eclispe66
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 24
Thanks anyway Ian.
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