Click here to Skip to main content
14,984,032 members
Articles / Desktop Programming / Win32
Posted 2 Aug 2012


11 bookmarked

Directory Monitor Service controls SSIS Package Execution

Rate me:
Please Sign up or sign in to vote.
5.00/5 (3 votes)
2 Aug 2012CPOL2 min read
WMI based DirectoryMonitor triggers SSIS Package execution.


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.


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 


  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
  3. installutil "C:\Folder1\Folder2\Yourservice.exe"


  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.
  3. 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
    2. net start Yourservicename
    3. Stop service
    4. 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"?>
    <add key="DirectoryToMonitor" value="C:\Test\DirMon">
      <!--Set the directory path to be monitored by the service -->
    <add key="WMIThreadSleepTimer" value="2">
      <!--Set the value of the ThreadSleep in Seconds -->
    <add key="WMISQueryInterval" value="2">
      <!--Set the value of the WMI Query Interval in Seconds -->
    <add key="ServiceLogSaveLocation" value="C:\Test\Logs">
      <!--Set the path to save service log entries -->
    <add key="EnableLogging" value="true">
      <!--Set true or false to enable/disable logging -->
    <add key="PackageToExecute" value="C:\test\KP\Package.dtsx">
      <!--Set the path for SSIS package to be executed by the service -->
    <add key="PackageLogSaveLocation" value="C:\Test\Logs">
      <!--Set the directory path to be monitored by the service -->
    <add key="SavePackageExecutionLog" value="true">
      <!--Set true or false to enable/disable -->

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()
        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()
                ConfigReader config = new ConfigReader();
                ServiceBase[] ServicesToRun;
                ServicesToRun = new ServiceBase[] 
                                    new DirectoryMonitorService(config)
            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. 


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Business Analyst
United States United States
No Biography provided

Comments and Discussions

QuestionSSIS Pin
Dlova9-Sep-14 3:25
MemberDlova9-Sep-14 3:25 
GeneralThank you Pin
Tammam Koujan30-Aug-13 9:22
professionalTammam Koujan30-Aug-13 9:22 
GeneralRe: Thank you Pin
Kuthuparakkal5-Sep-13 2:39
MemberKuthuparakkal5-Sep-13 2:39 
QuestionIssues Pin
svenb779-Aug-12 20:54
Membersvenb779-Aug-12 20:54 
AnswerRe: Issues Pin
Kuthuparakkal9-Aug-12 21:21
MemberKuthuparakkal9-Aug-12 21:21 
QuestionRe: Issues Pin
svenb779-Aug-12 23:31
Membersvenb779-Aug-12 23:31 
AnswerRe: Issues Pin
Kuthuparakkal10-Aug-12 0:22
MemberKuthuparakkal10-Aug-12 0:22 
QuestionRe: Issues Pin
svenb7710-Aug-12 1:14
Membersvenb7710-Aug-12 1:14 
AnswerRe: Issues Pin
Kuthuparakkal10-Aug-12 2:13
MemberKuthuparakkal10-Aug-12 2:13 
QuestionRe: Issues Pin
svenb7712-Aug-12 22:03
Membersvenb7712-Aug-12 22:03 
GeneralRe: Issues Pin
Kuthuparakkal18-Aug-12 18:44
MemberKuthuparakkal18-Aug-12 18:44 
QuestionFile Change Notification Pin
Dewey2-Aug-12 15:01
MemberDewey2-Aug-12 15:01 
AnswerRe: File Change Notification Pin
Kuthuparakkal2-Aug-12 15:30
MemberKuthuparakkal2-Aug-12 15:30 
GeneralRe: File Change Notification Pin
Dewey14-Aug-12 16:28
MemberDewey14-Aug-12 16:28 
GeneralRe: File Change Notification Pin
Kuthuparakkal18-Aug-12 18:43
MemberKuthuparakkal18-Aug-12 18:43 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.