Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

WMI task and WQL query Expand / Collapse
Author
Message
Posted Wednesday, August 1, 2012 7:58 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, July 24, 2014 4:11 AM
Points: 853, Visits: 787
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
Post #1338551
Posted Wednesday, August 1, 2012 11:54 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:14 PM
Points: 7,125, Visits: 12,719
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
Post #1338955
Posted Thursday, August 2, 2012 12:47 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, July 24, 2014 4:11 AM
Points: 853, Visits: 787
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
Post #1338968
Posted Thursday, August 2, 2012 8:08 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:14 PM
Points: 7,125, Visits: 12,719
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
Post #1339194
Posted Monday, October 22, 2012 3:33 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 8, 2013 12:36 PM
Points: 9, 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::Path], "\\", "\\\\") +"' AND TargetInstance.FileName LIKE '"+ @[User::FileNamePrefix] +"%' AND TargetInstance.Extension = '"+ @[User::FileExtension] +"'"

Post #1375753
Posted Monday, October 22, 2012 3:49 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:14 PM
Points: 7,125, Visits: 12,719
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
Post #1375758
Posted Monday, October 22, 2012 10:46 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, July 24, 2014 4:11 AM
Points: 853, Visits: 787
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
Post #1375823
Posted Tuesday, October 23, 2012 7:22 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 8, 2013 12:36 PM
Points: 9, 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.
Post #1376028
Posted Tuesday, October 23, 2012 7:40 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:14 PM
Points: 7,125, Visits: 12,719
What about my second question?

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1376043
Posted Tuesday, October 23, 2012 7:41 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 8, 2013 12:36 PM
Points: 9, Visits: 24
Thanks anyway Ian.
Post #1376046
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse