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
- Create a Windows Service and always monitor the required folder for changes. So the step is a combination of WMI events and Windows Service.
- Trigger a package based on the events from step 1.
Few basic tips related to Windows Service
Installation
- Open Visual Studio Command Prompt(Start>All Programs > Vistal Studio 20xx > Visual Studio Tools > Visual Studio Command Prompt.
- Type in the following at command prompt to install the service
installutil "C:\Folder1\Folder2\Yourservice.exe"
Uninstallation
- Open Visual Studio Command Prompt(Start>All Programs > Vistal Studio 20xx > Visual Studio Tools > Visual Studio Command Prompt.
- Type in the following at command prompt to uninstall the service.
installutil /u "C:\Folder1\Folder2\Yourservice.exe"
Start/stop Windows Service
- Open Command Prompt(Start>Run type in cmd will launch Command Prompt).
- Next
- Start the Service
net start Yourservicename
- 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:
- Change/modify the path for "Directory to Monitor"
- Control the WMI query interval time
- Control the WMI polling thread sleep time
- Control the package to be executed
- Control the location for service and package log path
All the above things can be controlled through simple configuration file below:
="1.0"="utf-8"
<configuration>
<appSettings>
<add key="DirectoryToMonitor" value="C:\Test\DirMon">
</add>
<add key="WMIThreadSleepTimer" value="2">
</add>
<add key="WMISQueryInterval" value="2">
</add>
<add key="ServiceLogSaveLocation" value="C:\Test\Logs">
</add>
<add key="EnableLogging" value="true">
</add>
<add key="PackageToExecute" value="C:\test\KP\Package.dtsx">
</add>
<add key="PackageLogSaveLocation" value="C:\Test\Logs">
</add>
<add key="SavePackageExecutionLog" value="true">
</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>();
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
{
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.