File Watcher Task

The task will detect changes to existing files as well as new files, both actions will cause the file to be found when available. A file is available when the task can open it exclusively. This is important for files that take a long time to be written, such as large files, or those that are just written slowly or delivered via a slow network link. It can also be set to look for existing files first (1.2.4.55).

The full path of the found file is returned in up to three ways:

• The ExecValueVariable of the task. This can be set to any String variable.
• The OutputVariableName when specified. This can be set to any String variable.
• The FullPath variable within OnFileFoundEvent. This is a File Watcher Task specific event.

Advanced warning of a file having been detected, but not yet available is returned through the OnFileWatcherEvent. This event does not always coincide with the completion of the task, as completion and the OnFileFoundEvent is delayed until the file is ready for use. This event indicates that a file has been detected, and that file will now be monitored until it becomes available. The task will only detect and report on the first file that is created or changes, any subsequent changes will be ignored.

Task properties and there usages are documented below:

Property	Data Type	Description
Filter	String	Default filter *.* will watch all files. Standard windows wildcards and patterns can be used to restrict the files monitored.
FindExistingFiles	Boolean	Indicates whether the task should check for any existing files that match the path and filter criteria, before starting the file watcher.
IncludeSubdirectories	Boolean	Indicates whether changes in subdirectories are accepted or ignored.
OutputVariableName	String	The name of the variable into which the full file path found will be written on completion of the task. The variable specified should be of type string.
Path	String	Path to watch for new files or changes to existing files. The path is a directory, not a full filename. For a specific file, enter the file name in the Filter property and the directory in the Path property.
PathInputType	FileWatcherTask.InputType	Three input types are supported for the path: Connection – File connection manager, of type existing folder. Direct Input – Type the path directly into the UI or set on the property as a literal string. Variable – The name of the variable which contains the path.
Timeout	Integer	Time in minutes to wait for a file. If no files are detected within the timeout period the task will fail. The default value of 0 means infinite, and will not expire.
TimeoutAsWarning	Boolean	The default behaviour is to raise an error and fail the task on timeout. This property allows you to suppress the error on timeout, a warning event is raised instead, and the task succeeds. The default value is false.

Installation

Installation requires Administrative privileges on install machine. The task is provided as an MSI file which you can download and run to install it. This simply places the files on disk in the correct locations and also installs the assemblies in the Global Assembly Cache as per Microsoft’s recommendations.

For 2005/2008 Only – You may need to restart the SQL Server Integration Services service, as this caches information about what components are installed, as well as restarting any open instances of Business Intelligence Development Studio (BIDS) / Visual Studio that you may be using to build your SSIS packages. Finally you will have to add the task to the Visual Studio toolbox manually. Right-click the toolbox, and select Choose Items…. Select the SSIS Control Flow Items tab, and then check the File Watcher Task in the Choose Toolbox Items window. This process has been described in detail in the related FAQ entry for How do I install a task or transform component?

We recommend you follow best practice and apply the current Microsoft SQL Server Service pack to your SQL Server servers and workstations.

Downloads

Please choose the version to match your SQL Server version, or you can install multiple versions and use them side by side if you are developing for multiple versions of SQL Server. For the recent releases of SQL Server Data Tools that can target different versions of SQL Server, please install all target server versions your intend to use.

File Watcher Task for SQL Server 2005

File Watcher Task for SQL Server 2008

File Watcher Task for SQL Server 2012

File Watcher Task for SQL Server 2014 – x64

File Watcher Task for SQL Server 2014 – x86

File Watcher Task for SQL Server 2016

File Watcher Task for SQL Server 2017

File Watcher Task for SQL Server 2019

Version History

SQL Server 2019

Version 7.0.3.22 – SQL Server 2019.
(29 Sep 2019)

SQL Server 2017

Version 6.0.3.22 – SQL Server 2017.
(29 Sep 2019)

SQL Server 2016

Version 5.0.3.22 – SQL Server 2016 GA release.
(8 Jun 2016)

SQL Server 2014

Version 4.0.3.22 – Updated installer including upgrade, Administrative install, extension mapping to support SSDT in Visual Studio 2015, and public Expression Editor control.
(8 Jun 2016)

Version 4.0.0.20 – SQL Server 2014 release. Includes upgrade support for both 2005, 2008 and 2012 packages to 2014.
(6 Sep 2014)

SQL Server 2012

Version 3.0.0.20 – Fixed UI bug, Could not load file or assembly ‘Microsoft.DataTransformationServices.Design, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91’ or one of its dependencies. The location assembly’s manifest definition does not match the assembly reference.
(19 Jun 2012)

Version 3.0.0.16 – SQL Server 2012 release. Includes upgrade support for both 2005 and 2008 packages to 2012.
(5 Jun 2012)

SQL Server 2008

Version 2.0.0.14 – Fixed user interface bug. A migration problem caused the UI type editors to reference an old SQL 2005 assembly.
(17 Nov 2008)

Version 2.0.0.7 – SQL Server 2008 release.
(20 Oct 2008)

SQL Server 2005

Version 1.2.6.100 – Fixed UI bug with TimeoutAsWarning property not saving correctly. Improved expression support in UI. File availability detection changed to use read-only lock, allowing reduced permissions to be used. Corrected installed issue which prevented installation on 64-bit machines with SSIS runtime only components.
(18 Mar 2007)

Version 1.2.5.73 – Added TimeoutAsWarning property. Gives the ability to suppress the error on timeout, a warning event is raised instead, and the task succeeds. (Task Version 3)
(27 Sep 2006)

Version 1.2.4.61 – Fixed a bug which could cause a loop condition with an unexpected exception such as incorrect file permissions.
(20 Sep 2006)

Version 1.2.4.55 – Added FindExistingFiles property. When true the task will check for an existing file before the file watcher itself actually starts. (Task Version 2)
(8 Sep 2006)

Version 1.2.3.39 – SQL Server 2005 RTM Refresh. SP1 Compatibility Testing. Property type validation improved.
(12 Jun 2006)

Version 1.2.1.0 – SQL Server 2005 IDW 16 Sept CTP. Futher UI enhancements, including expression indicator. Fixed bug caused by execution within loop Subsequent iterations detected the same file as the first iteration. Added IncludeSubdirectories property. Fixed bug when changes made in subdirectories, and folder change was detected, causing task failure. (Task Version 1)
(6 Oct 2005)

Version 1.2.0.0 – SQL Server 2005 IDW 15 June CTP. Changes made include an enhanced UI, the PathInputType property for greater flexibility with path input, the OutputVariableName property, and the new OnFileFoundEvent event.
(7 Sep 2005)

Version 1.1.2 – Public Release
(16 Nov 2004)

Screenshots

 

Troubleshooting

Make sure you have downloaded the version that matches your version of SQL Server, as each version of SQL Server has a separate download i.e. SQL Server 2014 vs SQL Server 2016.

If you an error when you try and use the task along the lines of The task with the name “File Watcher Task” and the creation name … is not registered for use on this computer, this usually indicates that the internal cache of SSIS components needs to be updated. This cache is held by the SSIS service, so you need restart the the SQL Server Integration Services service. You can do this from the Services applet in Control Panel or Administrative Tools in Windows. You can also restart the computer if you prefer. You may also need to restart any current instances of Business Intelligence Development Studio (BIDS) / Visual Studio that you may be using to build your SSIS packages. The full error message is shown below for reference:

TITLE: Microsoft Visual Studio
——————————
The task with the name “File Watcher Task” and the creation name “Konesans.Dts.Tasks.FileWatcherTask.FileWatcherTask, Konesans.Dts.Tasks.FileWatcherTask, Version=1.2.0.0, Culture=neutral, PublicKeyToken=b2ab4a111192992b” is not registered for use on this computer.
Contact Information:
File Watcher Task

For SQL Server 2012 and below, a similar error message can be shown when trying to edit the task if the Microsoft Exception Message Box is not installed. This useful component is installed as part of the SQL Server Management Studio tools but occasionally due to the custom options chosen during SQL Server 2005 setup it may be absent. If you get an error like Could not load file or assembly ‘Microsoft.ExceptionMessageBox.. you can manually download and install the missing component. It is available as part of the Feature Pack for SQL Server 2005 release. The feature packs are occasionally updated by Microsoft so you may like to check for a more recent edition, but you can find the Microsoft Exception Message Box download links here – Feature Pack for Microsoft SQL Server 2005 – April 2006

If you encounter this problem on SQL Server 2008, please check that you have installed the SQL Server client components. The component is no longer available as a separate download for SQL Server 2008  as noted in the Microsoft documentation for Deploying an Exception Message Box Application

The full error message is shown below for reference, although note that the Version will change between SQL Server 2005 and SQL Server 2008:

TITLE: Microsoft Visual Studio
——————————
Cannot show the editor for this task.
——————————
ADDITIONAL INFORMATION:
Could not load file or assembly ‘Microsoft.ExceptionMessageBox, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91’ or one of its dependencies. The system cannot find the file specified. (Konesans.Dts.Tasks.FileWatcherTask)

Once installation is complete you need to manually add the task to the toolbox before you will see it and to be able add it to packages – How do I install a task or transform component?

If you are still having issues then contact us, but please provide as much detail as possible about error, as well as which version of the the task you are using and details of the SSIS tools installed.

Sample Code

If you wanted to use the task programmatically then here is some sample code for creating a basic package and configuring the task. It uses a variable to supply the path to watch, and also sets a variable for the OutputVariableName. Once execution is complete it writes out the file found to the console.

/// <summary> /// Create a package with an File Watcher Task /// </summary> public void FileWatcherTaskBasic()
{
    // Create the package Package package = new Package();
    package.Name = "FileWatcherTaskBasic";

    // Add variable for input path, the folder to look in package.Variables.Add("InputPath", false, "User", @"C:\Temp\");

    // Add variable for the file found, to be used on OutputVariableName property package.Variables.Add("FileFound", false, "User", "EMPTY");

    // Add the Task package.Executables.Add("Konesans.Dts.Tasks.FileWatcherTask.FileWatcherTask, " +
        "Konesans.Dts.Tasks.FileWatcherTask, Version=1.2.0.0, Culture=neutral, PublicKeyToken=b2ab4a111192992b");
    // Get the task host wrapper TaskHost taskHost = package.Executables[0] as TaskHost;

    // Set basic properties taskHost.Properties["PathInputType"].SetValue(taskHost, 1); // InputType.Variable taskHost.Properties["Path"].SetValue(taskHost, "User::InputPath");
    taskHost.Properties["OutputVariableName"].SetValue(taskHost, "User::FileFound");

    #if DEBUG
    // Save package to disk, DEBUG only new Application().SaveToXml(String.Format(@"C:\Temp\{0}.dtsx", package.Name), package, null);
    #endif // Display variable value before execution to check EMPTY Console.WriteLine("Result Variable: {0}", package.Variables["User::FileFound"].Value);

    // Execute package package.Execute();

    // Display variable value after execution, e.g. C:\Temp\File.txt Console.WriteLine("Result Variable: {0}", package.Variables["User::FileFound"].Value);

    // Perform simple check for execution errors if (package.Errors.Count > 0)
        foreach (DtsError error in package.Errors)
        {
            Console.WriteLine("ErrorCode : {0}", error.ErrorCode);
            Console.WriteLine(" SubComponent : {0}", error.SubComponent);
            Console.WriteLine(" Description : {0}", error.Description);
        }
    else Console.WriteLine("Success - {0}", package.Name);

    // Clean-up package.Dispose();
}