Share via

SQL Server Integration Service: Using WMI Event Watcher to Trigger ETL on a File Drop Event on a Shared Folder

  • Article

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.

 

Technical Design

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

  1.  A folder "C:\WmifileWatcher" will be created.  User can drop fileon 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

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.