WMI task and WQL query

Question

WMI task and WQL query

Ian C0ckcroft

Hall of Fame

Points: 3373
More actions
August 1, 2012 at 7:58 am

#264556

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

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 1

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[/url].

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Ian C0ckcroft Hall of Fame Points: 3373 More actions · Answer 2

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 SSC Guru Points: 182279 More actions · Answer 3

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 Old Hand Points: 323 More actions · Answer 4

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] +"'"

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 5

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 Hall of Fame Points: 3373 More actions · Answer 6

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 Old Hand Points: 323 More actions · Answer 7

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 SSC Guru Points: 182279 More actions · Answer 8

What about my second question?

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

eclispe66 Old Hand Points: 323 More actions · Answer 9

eclispe66

Old Hand

Points: 323

October 23, 2012 at 7:41 am

#1551904

Thanks anyway Ian.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 10

The demo was done using SSIS 2012 so establish a baseline using the demo exactly as it was implemented, then change it for your environment.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

eclispe66 Old Hand Points: 323 More actions · Answer 11

I was attempting to try it As Is. I originally tried to make modifications for my particular package but could not get it to work so thought I would use it As Is so that I could figure out what I was doing wrong. I figured out why I was getting the 'invalid parameters' error. I entered the expressions incorrectly. So now it runs but it does not get past the File Watch Task which is the problem I was experiencing when I modified the code my for my package. The task just remains yellow.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 12

When adding a file to the drop directory are you copying and pasting the file into an otherwise empty directory? WMI is quite particular in that if you rename an existing file to a qualifying name, or paste on top of an existing file that has a qualifying name, an event may be fired but it is one other than __InstanceCreationEvent and the package will not proceed as some might expect.

You may also find this article[/url] useful. It's a different way to achieve the same using the Konesan's File Watcher Task.

If you do not like, or cannot because of environment constraints use third-party tools, I have an article set to be published on SSC on Thursday that shows how to implement a file watcher using only .NET in a Script Task. When that article is published (sometimes they get rescheduled so it might not be Thursday) I'll post a link to this thread in case you or anyone else is interested.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

eclispe66 Old Hand Points: 323 More actions · Answer 13

I just copied/pasted the file from another directory. I created a test directory so that I could execute your example for As Is. I will have to check with my manager regarding third party tools. I will look out for your new article.

Thanks.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 14

Hopefully one of the three techniques will work for you.

Using the Script Task in SSIS to Process Data Files When They Arrive[/url]

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato