Agenda

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 archieve the file to c:\wmifilewatcher_archieve

Physical Design

The first requirment 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.