Home > SSIS > File Watcher – SSIS

File Watcher – SSIS


WMI Event Watcher task

WMI Event Watcher Task in SSIS help to monitor or watch for event listed in Windows Management Instrumentation (WMI).

Management Instrumentation Query Language (WQL) event query to specify events the you wish to look for.

In this article, I will be showing you an interesting thing that is File watcher event. In ETL (SSIS) world everyone deals with data file as a data source.

But, in many case we may get is as we request from a team in organization. But, in other case or in the automated environment SSIS  package look for the file to reach a specified folder and then it will load the data in to database.

Implementation

A folder named WMI\WMIFileWatcher. My package is designed to address a file watcher event. In this, my package will look for a file to be copied in the folder and shows a message box as an alert.

Step 1: Create a package and add WM Connection manager.

Step 2: Configure WMI connect in WMI event watcher task.

  • Goto WMI Options
  • Select the connect from the drop down list box
  • Paste this query into the WqlQuerySource property

SELECT * FROM __InstanceCreationEvent WITHIN 10 WHERE
TargetInstance ISA “CIM_DirectoryContainsFile” and TargetInstance.GroupComponent= “Win32_Directory.Name=\”c:\\\\WMIFileWatcher\””

    • In this query folder path and drive path is static and we can customize is as per our need.
Step 3: Add Script task and add this script MsgBox(“You have got a file”) in the code section.

 Step 4: connect WMI event watcher task with Script task and execute the package.

Note: this package will watch for the file and will never end until you copy a file to that folder or manually stop the package.

Updated on 2/6/2013: You can learn more about File System Monitor using WMI here.

Thanks for reading.

Advertisements
  1. Mike
    October 12, 2011 at 8:44 am

    can you post the query again, it is cut off at the end….thanks

  2. October 12, 2011 at 7:42 pm

    Thanks Mike, Query is below the picture on step 2.

  3. Prasad
    October 13, 2011 at 9:55 pm

    HI Ayyappan thanks. This is really very useful in lot of cases. Just one query is it with Denali?

  4. Martin
    November 8, 2011 at 2:23 pm

    I just wonder if there is a flag indicates that the file is ready(not copying, meaning,the file is being used by another process)
    As for large file, say, 1 GB, whatever time you set in the query(within xx seconds)…..while the file is adding to the folder, but not yet ready..

  5. November 8, 2011 at 3:53 pm

    Hi Martin,

    You can use this command to check the file status.
    C:>wmic datafile where name=’your file name name with path’

    Please read this for more information
    http://msdn.microsoft.com/en-us/library/windows/desktop/aa394594(v=VS.85).aspx

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: