Agenda


This case study will teach you how you can use WMI (Windows Management Instrumentation) to monitor a file drop
event on a shared folder. We will build a solution that will keep watch on  a "shared folder" and then trigger an ETL when
a file gets drop or copied into it and then process the file and then load it to a database server.

Table of Contents



Technical Design

Below is a high-level technical design for this case study:

1.  A folder "C:\WmifileWatcher" will be created.  User can drop file
on this folder using FTP, network fileshare or simply as local folder for testing

2. A file name "employee.csv" that contains employee data will be dropped into the shared drive

3. An SSIS package will keep watch on the file drop(copy) event and then it will process the employee.csv
and load it to the employee table in the HRIS database

4. When loading to SSIS is complete it will archive the file to c:\wmifilewatcher_archive

Physical Design

The first requirement is to build the physical folder "C:\WmifileWatcher" which is rather easy so
we'll skip it.

A.  Create a new  the SSIS Package and Add the WMI Event Watcher task

To be able to watch for a file drop event you should build an SSIS and add a WMI task as shown.

B. Add and Configure a new WMI Connection to the solution

You need to configure a WMI connection to the server which you want to monitor in this case it will be
the localhost.


C. Configure the WMI Query

Configure the WMi query source with the following
SELECT * FROM __InstanceCreationEvent WITHIN 10 WHERE TargetInstance ISA "CIM_DirectoryContainsFile"
and TargetInstance.GroupComponent= "Win32_Directory.Name=\"c:\\\\WMIFileWatcher\"" .

This will monitor the C:\WMIFilewatcher folder for changes




D. Run The Package and Drop a File.

Basically you can now Run the Package and drop a file






D. Run The Package and Drop a File.

Basically you can now Run the Package and drop a file


This will serve as a trigger to look into the folder. And we are sure enough that there is a file in there.

E. Add a for each loop to iterate each file  in the shared folder.

Add a For each loop container and configure it to browse each file in the shared folder.




E.  Assign the Files to variables inside the for each loop.


If you are seasoned SSIS developer you can already proceed from here. Assigned the files to variable.