Friday, 14 November 2014

Case
I want to use the WMI Event Watcher Task to watch for new files in SSIS, but it doesn't allow me to watch subfolders and after catching an event it continues with the next task, but meanwhile it doesn't watch for new files until in starts again. Is there an alternative?

WMI Event Watcher Task

Solution
An alternative could be to create a Windows Service that does the watching part and then executes a package when a new file arrives. For this example I used the full version of Visual Studio 2010 (SSDT BI is not enough). The example is in C#, but you can easily convert that to VB.NET.

1) Windows Service Project
Start Visual Studio and create a new Windows Service Project. I used the 4.0 framework since I want to execute SSIS 2012 packages. For 2008 you can use 3.5.

Windows Service 4.0 C#

2) App.Config
To avoid hardcoded connection strings, paths and filters, we first have to add an Application Configuration File (App.config). Right Click the project in the solution explorer, choose Add, New Item... and then Application Configuration File. After adding the app.config we need to edit it. You can copy the XML below to start with and add extra parameters at the end. Make sure the values are correct.

3) Add References
Right click references in the Solution Explorer and choose Add Reference... For the configuration part we need to add two extra references that can be found in the .NET tab:
- System.Configuration.dll
- System.Configuration.Install.dll

Add References

For executing the SSIS packages we need four extra references that can be found in the GAC (MSIL) folder. The exact place could vary, but here are mine for SSIS 2012. Use the browse tab in the Add Reference Window to add them.C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.ConnectionInfo\11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.ConnectionInfo.dllC:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Management.Sdk.Sfc\11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Management.Sdk.Sfc.dllC:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Smo\11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Smo.dllC:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Management.IntegrationServices\11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Management.IntegrationServices.dll

4) Services1.cs
Now open the source code of Services1.cs to add the actual Windows Services code. In the grey designer surface press F7 or click on the link to switch to code view. Copy the code below. If you don't want to use the Project Deployment Model then you must slightly change the code in the watcher_Created method.

5) Installer.cs
To make the Windows Services installable we need to add an Installer Class by right clicking the Solution Explorer and choose Add, New Item..

Installer.cs

6) Add Service(Process)Installer
Next you can drag ServiceInstaller and ServiceProcessInstaller from the toolbox to the designer surface of install.cs. If they are not available in the toolbox then you need to add them manually by right clicking the toolbox and choose Choose Items... (just like in SSIS 2008 when adding Third Party tasks).

Service(Process)Installer

7) Edit Service(Process)Installer
In the properties of serviceInstaller1 you can set values for properties like ServiceName, DisplayName and the Description of the service.

Service Properties

8) Building & Deploying
Now build the project in release mode (via build menu or Ctrl + Shft + B) and go to the bin release folder of your project. Copy these files to a suitable location for the service.

Bin Release Folder

9) InstallUtil
Now open the Visual Studio 201X command prompt as Administrator (otherwise you don't have rights to add a new service). In the command prompt go to the folder where the service is located.
Then enter the following command: installutil FileWatcherForSSIS.exe. During installation it will ask for the account that runs the service. The user must be able to read the folder and to execute the package (just like the user in a SQL Server Agent jobstep). For your production server without Visual Studio (and without installutil.exe) you need to create a setup project in Visual Studio to install your new Windows Services.

Visual Studio Command Prompt (run as Administrator)

10) Start Service and test
Now go to your local services and start the new Windows Service. Then start watch the log while adding a new file in the folder that you are watching.