Trigger SSIS package when files available in a Folder part#2

In continuation to my previous post

Trigger SSIS package when files available in a Folder

Windows Service Approach

Open Microsoft VisualStudio 2010, select New Project and click on Windows Service.

I named the solution as FileWindowsService, right click on References and select ‘Add Reference…’

Under .NET tab select System.Configuration , System.Configuration.Install and System.management

System.Configuration namespace allows us to handling the configuration data.
System.Configuration.Install namespace allows us to write custom installers for our own components.
System.management namespace allows us to Provides access to a rich set of management information and management events about the system, devices, and applications instrumented to the Windows Management Instrumentation (WMI) infrastructure.

Press Ctrl+Shift+A to add Application Configuration File or else follow the figures to add configuration file through which we will the pass the path of the directory to watch.

Follow the below figure to add directory path in the configuration file.

<?xml version="1.0" encoding="utf-8" ?>

<configuration>

<appSettings>

<add key="WatchPath" value="F:\\DellmeSoon\\" />

</appSettings>

</configuration>

Now open Service1.cs[Design] and drag FileSystemWatcher from the Toolbox which is in Components group.

FileSystemWatcher component is to watch for changes in a specified directory. You can watch for changes in files and subdirectories of the specified directory. You can create a component to watch files on a local computer, a network drive, or a remote computer.

Right Click on fileSystemWatcher1 tool and select properties. Change the Name and the NotifyFilter properties to FileName, DirectoryName, Size, LastWrite, LastAccess, CreationTime, Security

If you want to include subdirectories of the source folder that enable that property.

To watch for changes in all files, set the Filter property to an empty string ("") or use wildcards ("*.*").To watch a specific file, set the Filter property to the file name or to watch only certain file formats set the Filter Property to file extension like “.txt” for text files. You can watch for changes in Attributes, the LastWrite date and time, or the Size of files or directories. This is done by setting the NotifyFilters property to one of the NotifyFilters values.

Add Microsoft.SqlServer.ManagedDTS reference and add a using statement for the Microsoft.SqlServer.Dts.Runtime namespace.

Add another using statement for System.Configuration

   1: using System;

   2: using System.Collections.Generic;

   3: using System.ComponentModel;

   4: using System.Data;

   5: using System.Diagnostics;

   6: using System.Linq;

   7: using System.ServiceProcess;

   8: using System.Text;

   9: using System.Configuration;

  10: using Microsoft.SqlServer.Dts.Runtime;

11:

  12: namespace FileWindowsService

  13: {

  14:     public partial class Service1 : ServiceBase

  15:     {

  16:         public Service1()

  17:         {

  18:             InitializeComponent();

  19:         }

  20:         protected override void OnStart(string[] args)

  21:         {

  22:             FileWindowService.Path = ConfigurationManager.AppSettings["WatchPath"];

  23:         }

  24:         protected override void OnStop() { }

  25:         private void FileWindowServiceOnChanged(object sender,System.IO.FileSystemEventArgs e) { }

  26:         private void FileWindowServiceOnCreated(object sender,System.IO.FileSystemEventArgs e)

  27:         {

  28:             Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();

  29:             Package createInvoice = app.LoadFromSqlServer("\\MyPackage", "MyProducitonServer", null, null, null);

  30:             //createInvoice.Variables["PeriodID"].Value = VariableValue;

  31:             DTSExecResult result = createInvoice.Execute();

  32:         }

  33:         private void FileWindowServiceOnDeleted(object sender,System.IO.FileSystemEventArgs e) { }

  34:         private void FileWindowServiceOnRenamed(object sender, System.IO.RenamedEventArgs e) { }

  35:      }

  36:   }

Above C# code will triggers the events associated with file Created, Changed, Deleted and Renamed.

I have added code to call a SSIS package when the file is placed in the given source folder.

We need to map the methods in C# code to the events in the design pane.

In Services1.cs[Design] click on FileWindowService and select properties, click on events and map the respective methods to the event. Like FileWindowServiceonCreated to the Created event.

We have done with the code, now we need to create windows installer class to allow the project to be compiled as a Windows service.

Now add Installer Class (Follow the process of adding Configuration file)

Drag two components ServiceInstaller and ServiceProcessInstaller, if these components are located in the tool box, tight click on the tool box and the “choose items…” in .NET Framework Components

Select the properties of ServiceInstaller1 and set the ServiceName property to FileWindowService

Select the properties of serviceProcessInstaller1 and set the Account property to LocalSystem, which enables the service to be run as a local system account.

Now, simply Build FileWindowService (from the Build menu), and all necessary installer files will be created within the project directory. To install the newly created service, you must use the .NET Framework InstallUtil program.

.exe will be generated in the bin\debug of the project path.