Directory Monitor Service controls SSIS Package Execution

• Download DirectoryMonitorService-noexe.zip - 22.1 KB
• Download DirectoryMonitorService.zip - 92.8 KB

Introduction

Let me introduce a scenario where we need to trigger an SSIS package if we add new file to a specific directory. Here we need to have a monitor service run always and detect the change and trigger the package. So the article explains how this can be implemented.

Background

This article is an answer to the comments/questions recieved for my another article Dynamic Excel file loading with SSIS.

Using the code 

Let's define a few important aspects of the objective to accomplish

1. Create a Windows Service and always monitor the required folder for changes. So the step is a combination of WMI events and Windows Service.
2. Trigger a package based on the events from step 1.

Few basic tips related to Windows Service 

Installation 

1. Open Visual Studio Command Prompt(Start>All Programs > Vistal Studio 20xx > Visual Studio Tools > Visual Studio Command Prompt.
2. Type in the following at command prompt to install the service

installutil "C:\Folder1\Folder2\Yourservice.exe"

Uninstallation 

1. Open Visual Studio Command Prompt(Start>All Programs > Vistal Studio 20xx > Visual Studio Tools > Visual Studio Command Prompt.
2. Type in the following at command prompt to uninstall the service.

installutil /u "C:\Folder1\Folder2\Yourservice.exe"

Start/stop Windows Service 

1. Open Command Prompt(Start>Run type in cmd  will launch Command Prompt).
2. Next
1. Start the Service

net start Yourservicename

2. Stop service

net stop Yourservicename

Modify Settings

Any dynamic settings modification(example modifying configuration entries) would be reflected only after you do a stop and start the service. 

Formulating the WMI Query

Let's build a query to monitor any file creation(you can copy file to the location too):

string WMIQuery = "SELECT * FROM __InstanceCreationEvent WITHIN 10 " 
                                    + " WHERE TargetInstance ISA \"CIM_DirectoryContainsFile\" and "
                                    + " TargetInstance.GroupComponent= "
                                    + " \"Win32_Directory.Name='C:\\Test\\MyFolderToMonitor'\"";  

The above WMI query will capture any file creation event occured within 10 seconds for the folder mentioned. 

Executing SSIS from C#

We can use Microsoft.SQLServer.ManagedDTS (normal location is : C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SQLServer.ManagedDTS.dll ) library to execute the package from C#. 

Lets build little class to do this. Please see the following class to see how it works. It is as simple as that Load the package at the path supplied, and use Execute method of Package object. 

using Microsoft.SqlServer.Dts.Runtime;
 
namespace DirectoryMonitorService
{
    public class PackageExecuter
    {
        Package pkg;
        Application app;
        DTSExecResult pkgResults;
 
        public PackageExecuter()
        {
        }       
 
        public void Run()
        {
            app = new Application();
            pkg = app.LoadPackage("PackagePath", null);
            pkgResults = pkg.Execute();
        }
    }
}

How to make the Service Flexible 

You should be able to:

1. Change/modify the path for "Directory to Monitor"
2. Control the WMI query interval time
3. Control the WMI polling thread sleep time
4. Control the package to be executed
5. Control the location for service and package log path

All the above things can be controlled through simple configuration file below:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
<appSettings>
    <add key="DirectoryToMonitor" value="C:\Test\DirMon">
      <!--Set the directory path to be monitored by the service -->
    </add>
    <add key="WMIThreadSleepTimer" value="2">
      <!--Set the value of the ThreadSleep in Seconds -->
    </add>
    <add key="WMISQueryInterval" value="2">
      <!--Set the value of the WMI Query Interval in Seconds -->
    </add>
    <add key="ServiceLogSaveLocation" value="C:\Test\Logs">
      <!--Set the path to save service log entries -->
    </add>
    <add key="EnableLogging" value="true">
      <!--Set true or false to enable/disable logging -->
    </add>
    <add key="PackageToExecute" value="C:\test\KP\Package.dtsx">
      <!--Set the path for SSIS package to be executed by the service -->
    </add>
    <add key="PackageLogSaveLocation" value="C:\Test\Logs">
      <!--Set the directory path to be monitored by the service -->
    </add>
    <add key="SavePackageExecutionLog" value="true">
      <!--Set true or false to enable/disable -->
    </add>
</appSettings>
</configuration>

Managing/Handling configuration

We can build a small class to manage the configuration and keep it ready for whenever we want ie without actually going to config again.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml.Linq;
using System.Xml;
using System.Configuration;
using System.Collections.Specialized;
using System.Collections;
 
namespace DirectoryMonitorService
{
    public class ConfigReader
    {
        const string DirectoryToMonitor = "DirectoryToMonitor";
        const string WMIThreadSleepTimer = "WMIThreadSleepTimer";
        const string ServiceLogSaveLocation = "ServiceLogSaveLocation";
        const string WMISQueryInterval = "WMISQueryInterval";
        const string EnableLogging = "EnableLogging";
        const string SavePackageExecutionLog = "SavePackageExecutionLog";
        const string PackageToExecute = "PackageToExecute";
        const string PackageLogSaveLocation = "PackageLogSaveLocation";
        const string PackageProperties = "/configuration/PackageProperties";
        const string ServiceProperties = "/configuration/ServiceProperties";
       
        private string _directoryPath = "";
        public string DirectoryPath
        {
            get { return _directoryPath; }
            set { _directoryPath = value; }
        }
 
        private string _configPath = "";
        public string ConfigPath
        {
            get { return _configPath; }
            set { _configPath = value; }
        }
 
        private bool _saveLog = false;
        public bool SaveLog
        {
            get { return _saveLog; }
            set { _saveLog = value; }
        }
 
        private string _logPath = "";
        public string LogPath
        {
            get { return _logPath; }
            set { _logPath = value; }
        }
 
        private int _threadSleep=2000;
        public int ThreadSleep
        {
            get { return _threadSleep; }
            set { _threadSleep = value; }
        }
        private int _queryInterval=10;
        public int QueryInterval
        {
            get { return _queryInterval; }
            set { _queryInterval = value; }
        }
 
        private string _packagePath = "";
        public string PackagePath
        {
            get { return _packagePath; }
            set { _packagePath = value; }
        }
 
        private bool _saveResults = false;
        public bool SaveResults
        {
            get { return _saveResults; }
            set { _saveResults = value; }
        }
 
        private string _packageExecutionResultSavePath = "";
        public string PackageExecutionResultSavePath
        {
            get { return _packageExecutionResultSavePath; }
            set { _packageExecutionResultSavePath = value; }
        }
 
        private Dictionary<string, string> execArgs;
        public ConfigReader()
        {
            BuildConfiguration();
        }
        private void BuildConfiguration()
        {
            execArgs = new Dictionary<string, string>();
 
            // Get the appSettings.
            NameValueCollection appSettings = ConfigurationManager.AppSettings;
 
            IEnumerator appSettingsEnum = appSettings.Keys.GetEnumerator();
            int i = 0;
            Console.WriteLine("App settings.");
            while (appSettingsEnum.MoveNext())
            {
                string key = appSettings.Keys[i];
                string value = appSettings[key];
                execArgs.Add(key, value);
                i += 1;
            }
 
            this.DirectoryPath = execArgs[DirectoryToMonitor];
            this.SaveLog = bool.Parse(execArgs[EnableLogging]);
            this.LogPath = execArgs[ServiceLogSaveLocation];
            this.ThreadSleep = Int32.Parse(execArgs[WMIThreadSleepTimer]) * 1000;
            this.QueryInterval = Int32.Parse(execArgs[WMISQueryInterval]);
 
 
            this.PackagePath = execArgs[PackageToExecute];
            this.SaveResults = bool.Parse(execArgs[SavePackageExecutionLog]);
            this.PackageExecutionResultSavePath = execArgs[PackageLogSaveLocation];
        }
 
    }
}

The Entry Point 

Lets define this with full fledge flexibility to log events:

using System;
using System.Collections.Generic;
using System.Linq;
using System.ServiceProcess;
using System.Text;
using System.Diagnostics;
using System.Configuration;
 
namespace DirectoryMonitorService
{
    static class Program
    {
        /// <summary>
        /// The main entry point for the application.
        /// </summary>
        static void Main()
        {
 
            try
            {
                ConfigReader config = new ConfigReader();
                ServiceBase[] ServicesToRun;
                ServicesToRun = new ServiceBase[] 
                                { 
                                    new DirectoryMonitorService(config)
                                };
                ServiceBase.Run(ServicesToRun);
            }
            catch (Exception ex)
            {
                EventLog m_EventLog = new EventLog("");
                m_EventLog.Source = "DirectoryMonitorService";
                m_EventLog.WriteEntry(ex.ToString(), EventLogEntryType.Error);
 
            }
        }
 
    }
}

Please find full fledged working code attached.