File Watcher Task

by Darren Green 6 Sep 2014 16:07

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

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.

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.

For 2005/2008 Only - 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

The File Watcher Task  is available for SQL Server 2005, SQL Server 2008 (includes R2) and SQL Server 2012. Please choose the version to match your SQL Server version, or you can install multiple versions and use them side by side if you have more than one version of SQL Server installed.

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

Version History

SQL Server 2014

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

File Watcher Task Editor dialog 

Troubleshooting

Make sure you have downloaded the version that matches your version of SQL Server. We offer separate downloads for SQL Server 2005 and SQL Server 2008.

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

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();
}

(Updated installation and troubleshooting sections, and added sample code July 2009)

Comments (101) -

11/4/2008 8:34:22 PM #

Rags

Hi,
I have been using the File watcher in an SSIS package, however the Filewatcher does not notify File events when an existing file in the folder is renamed. The Filewatcher is set to look for an extension .txt and if there is a existing file of extension .tmp which is later on renamed to .txt, the filewatcher does not raises events for it.

Could you please let me know how to get around this.

-Thx
Sam

Rags United States

11/5/2008 7:28:39 PM #

Darren Green

Sam, the task does not monitor for the rename event. People often use the renaming of a file to signal that the file is available, to prevent issues with consumers trying to use a file whilst it is still being written too. The File Watcher Task explicitly checks for the file being available, and does not return until it is, so by design I did not include rename support.

Darren Green United Kingdom

11/10/2008 7:50:47 AM #

Xavier S

Hi. Just installed the file watcher task for SQL 2005. Created a new packages in SSIS and tried using file watcher task and it sent me an error message that states that it is not registered for use in this computer. Is this a free version, is there need for a registration? I'd appreciate any information. Thank you
Xavier S.

Xavier S United States

11/12/2008 11:34:23 AM #

Graeme

I have downloaded and installed FileWatch for 2.0.0.7 for SQl Server 2008.  When I try and modify and of the variable properties (Output Variable name, Path Variable) I get the following error: "Could no load file or assembly Microsoft.DataTransformationServices.Design, Version 9.0.242.0 ........etc"
It appears to be trying to load 2005 version assembly.


Graeme United Kingdom

11/17/2008 3:10:49 PM #

Larry

I also can not find the editor.....

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)

Larry United States

11/17/2008 8:37:20 PM #

Darren Green

File Watcher Task for SQL Server 2008 - Version 2.0.0.14 Released. This fixes the user interface bug, "Cannot show the editor for this task" when you use the type editors . Thanks to Graeme and Larry for highlighting this issue.

Darren Green United Kingdom

1/22/2009 12:39:36 PM #

Kevin

Hello, I have been using the latest 2005 version for a year now and all of a sudden now about once a week the FWT seems to get stuck on the same file. I know this because the file we are waiting on was 1 gig and is always the last file to be received. This has happened with this same file now for the last month. any thoughts?

Kevin United States

1/22/2009 1:05:17 PM #

Allan Mitchell

Kevin.  Are you running the FSW in a loop?  If you drop in the 1GB file all by itself does the routine work?  In the task we raise 2 events.  one is when we see the file (OnFileWatcherEvent) and one is when you can have it(OnFileFoundEvent).  I am interested about the definition of "Stuck".  

Allan Mitchell Germany

1/27/2009 9:51:24 PM #

Kevin

the FSW isn't running in a loop. I have 11 different ones running for 11 different files so I can bring them in as soon as they are available. By stuck I mean the FSW either never sees the file come in or never sees it get released from the app dropping the file. I have tried to remove the file and put it back in there but that doesn't work. I have to stop and restart the job.
thanks,

Kevin United States

1/28/2009 2:29:40 PM #

Allan Mitchell

Interesting.  I can honestly say I have never seen this.  If the 1GB file is the only one dropped do you get the same problem? Is the location of the drop the same (same server type etc) as the other files that pickup correctly?  is this everytime?

This is not really a forum so would prefer we take this offline (support AT konesans . COM)

Allan Mitchell Germany

2/10/2009 8:10:22 AM #

Rene Wimmers

I've installed File Watcher for SQL Server 2005 and added it to my toolbox.
However, when I double click it, it gives the following error:
"The task with the name "File Watcher Task" and the creation name "Konesans ....etc is not registered for use on this computer". I've seen that the PublicKeyToken differs from all the other toolbox items.
Anyone out there with an idea?

Rene Wimmers Netherlands

2/19/2009 2:37:38 PM #

Ish

Hi,
I have been using the File watcher in an SSIS package, however the Filewatcher does not fire OnFileWatcher event if the file is not available eg., file is open.

Could you please let me know when does OnFileWatcher event gets fired?

-Thx
Ish

Ish United Kingdom

3/18/2009 10:14:54 PM #

scoo

Did anyone come up with solution for 'not registered for use' problem?

scoo

3/19/2009 3:51:50 PM #

scoo

Never mind... just reboot your pc

scoo

4/24/2009 3:24:39 AM #

steve

having problems with the File Watcher Task... when we build a package for deployment and then install the package on another machine... "no path value has been specified" during validation steps... the configuration file has been edited to synch all respective values to the new machine... also, a package without File Watcher, when installed and config edited... works just fine.

...any help would be greatly appreciated. very stuck at this point...

added: The package, with file watcher, when installed on the same machine it was created on, works...  

steve United States

4/29/2009 11:05:03 AM #

Zoran

Nice thing but I just noticed when I drop at the same time more then one file it populates output variable with the last file received (alphabetically). It is in the loop container and I expected it will loop through all files one by one. Did I do something wrong?

Zoran United Kingdom

4/30/2009 12:42:12 PM #

Darren Green

Zoran, by design the file watcher task will only find one file at a time. The task is not designed for multiple files at the same time or other high frequency scenarios. If we did extend it the next problem would be the serialised nature of processing, and there is now way a SSIS task we could sensibly offer any form of concurrency. In this type of scenario I suggest you look at a file watcher process external to SSIS. This processes could handle the multiple file scenario much better, and then do sensible things like fire off multiple threads to control the handling of each file, perhaps by executing a SSIS package. The problem your trying to solve is perfectly sensible and quite common, but doing it inside of SSIS with a task would be the wrong way to do it.

Darren Green United Kingdom

5/27/2009 2:28:36 PM #

Vineet

hi Darren Green ,

I am using File Watcher Task in my SSIS and I tested with multiple files . As you commented I feel File Watcher fires multiple events for multiple files at the same time.

Thanks,
Vin

Vineet Canada

6/3/2009 10:47:13 AM #

Kristof

Hi Darren,

I've just downloaded the 2008 version of your File Watcher Task. However, when I try to open the edit window it gives me the following error message:

Could not load file or assembly 'Microsoft.ExceptionMessageBox, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. Das System kann die angegebene Datei nicht finden. (Konesans.Dts.Tasks.FileWatcherTask)

A quick look into my assembly folder confirmed the message as there's no such assembly existent. I also can't find the Microsoft.ExceptionMessageBox.dll on my system. SSIS 2008 and .net Framework 3.5 are installed.

Is this a bug or did I miss something? The rest of your components are working fine.

Kristof Germany

6/18/2009 10:43:21 PM #

Cherie

I am VERY new to SQL and SSIS.  This tasks sounds like it will do exactly what I want, which is once a file arrives set off the rest of the SSIS tasks.  I downloaded the task and have it in my toolbox, however I don't see any step-by-step on how I set this up.  I see lots of bits and pieces, but I don't understand the variables, or how or where they are used. We're a small company that just moved from SQL 2000 & DTS to SQL 08 & SSIS - and I'm needing all the help I can get.  Any basic instructions?

Cherie United States

6/18/2009 10:44:22 PM #

rachel

Like it

rachel United States

6/23/2009 3:22:48 PM #

STeve Tahmosh

Hi,
I've been using this task for SQL 2005, and it works great.
I had some issues with the file still being held once the FileWatcher finds it.
I resolved that with a 2-minute sleep immediately after the filewatcher releases.
Not sure why the filewatcher releases when the file is still "held".
Our files are very small, and the first thing we do after the filewatcher finds the file is move it to a staging area (this is what fails).
In any case, just one other question:
When the FileWatcher "times out" it throws an OnError
I have a generic OnError handler that emails a "fatal error".
I have a Success (file found) and Failure (no file found in watch time), branch.
The failure sends an email (no file received in window)
The Generic On-Error also sends a generic email (SSIS error)
Is there any way to "trap" the File Watcher Timeout?
Thanks,
STeve

STeve Tahmosh United States

6/24/2009 11:39:52 AM #

Allan Mitchell

Hi STeve

We raise 2 events on this task.  The first is to tell you we have seen the file being dropped and the second is to to tell you that you can go ahead and get a lock on the file.  Are you trying to do your stuff in the right event?  You can change the behavior of the timeout by setting the TimeoutAsWarning property.

Allan Mitchell Germany

7/13/2009 10:29:09 PM #

K Fast

Has anyone ever tried using the File Watcher task programmatically? I've tried to reverse engineer the entire process by first using BIDS to run the File Watcher and then a Script task on the OnFileFoundEvent event. Everything seems to be going good on the BIDS side but in code I just can't get it to work. Don't think the OnFileFoundEvent is firing correctly in code because the OutputVariableName variable never gets the value for the path of the file.

I can post my code but it's rather long. Here is some of it...


    Private Sub FileWatcher2()
        Dim pkg As New Microsoft.SqlServer.Dts.Runtime.Package

        pkg.Executables.Add( _
        GetType(Konesans.Dts.Tasks.FileWatcherTask.FileWatcherTask).AssemblyQualifiedName)

        Dim tskhost As Microsoft.SqlServer.Dts.Runtime.TaskHost
        Dim tskFileWatcher As Konesans.Dts.Tasks.FileWatcherTask.FileWatcherTask

        Dim ehandler As DtsEventHandler

        tskhost = CType(pkg.Executables(0), Microsoft.SqlServer.Dts.Runtime.TaskHost)
        tskhost.Description = "File Watcher Task"
        tskhost.Variables.Add("FileFound", True, "User", "")
        tskhost.Variables.Add("directoryPath", True, "User", "C:\Temp")
    
        ehandler = tskhost.EventHandlers.Add("OnFileFoundEvent")

        tskFileWatcher = CType(tskhost.InnerObject, Konesans.Dts.Tasks.FileWatcherTask.FileWatcherTask)

        tskFileWatcher.OutputVariableName = "User::FileFound"
        tskFileWatcher.PathInputType = Konesans.Dts.Tasks.FileWatcherTask.InputType.Variable
        tskFileWatcher.Path = "User::directoryPath"

        'The code pauses right here until I drop a file into the c:\temp directory
        pkg.Execute()

        'I then loop through all the variables after the file is dropped in c:\temp
        'Both "FileFound" and "FullPath" come up empty. They should contain the full name of the file I added to c:\temp
        Console.WriteLine("Error Handler Variables")
        For Each pVariable In ehandler.Variables
            Console.WriteLine(pVariable.Name & ": " + pVariable.Value.ToString)
        Next

        pkg.Dispose()
    End Sub





  

K Fast Canada

7/21/2009 12:06:29 PM #

Darren Green

K Fast, I think you have got confused with the event handler. The event will be firing, and in the event the variable FullPath will be set, but you are not actually using it. You have setup the task using the OutputVariableName property correctly, so all you need to do is look at that, but look at them on the package variables collection. I have added some sample code, sorry it is C#, that does just this.

I have also updated troubleshooting and installation sections for some of the issues commented on previously.

Darren Green United Kingdom

7/21/2009 5:01:28 PM #

devlopix

Task works quite fine for local directories, but not for UNC Paths.
Path is: \\server\sharename
Option: Find existing files=false
Task recognizes no new files

With
Option: Find existing files=true
File is found

plz help

devlopix Austria

7/21/2009 5:15:55 PM #

devlopix

Ok, found the reason:
"Watcher task also requires permissions to take out an exclusive read/write lock on the file"
which my import sysaccount does not have on this share.

devlopix Austria

7/28/2009 6:48:10 PM #

K Fast

Thanks for the help Darren. Your code will be very helpful to anyone using the task programmatically.

My code wasn't that far off. It overlooked something very basic. All I had to do was change this line...

tskhost.Variables.Add("FileFound", True, "User", "")

to this...

tskhost.Variables.Add("FileFound", False, "User", "")

I was setting the Variable as read only so the path was never getting written there! I can't believe how much time I wasted over it.

K Fast Canada

8/12/2009 9:25:30 PM #

Guru

Hi all,

I have SQL 2005 client, when I try to install this, it gives an error saying
SSIS is not there.
Does this actually looks for the SSIS services.
Is there any other file for client.

Any help is appreciated.

Thanks

Guru United States

8/18/2009 3:58:48 PM #

Lou

I have created a package that uses the filewatcher task at the beginning of the package. Once the packed is run the filewatcher waits until a file is dropped then allow the package to flow, my question is how do i get this package to run again after it has executed once?

I  need the package to be constantly watching for files. Any ideas?

Lou Canada

8/20/2009 10:34:47 AM #

Darren Green

Lou, you can put the task in a loop. You will need to set the FindExistingFiles property to true so that it looks for files that may have arrived whilst it was processing another file. By design the task will only handle one file at a time, then it completes. Trying to do the always on multiple instance monitoring files all inside a single task would be like trying to build SSIS into a single task, probably not a good idea.

Whilst the task could do more like watching multiple files or keep running, it makes no sense from a SSIS control flow perspective. To start a new task on success of the file watcher it has to finish, so it cannot keep running as well, the task either completes or it does not. If you have a high volume of files, or want multiple files to be processed in parallel then the task is probably not for you, and a bespoke solution would be better. I would write an external file monitor, perhaps as a Windows service, and then kick off a SSIS package for each file found.

Darren Green United Kingdom

8/25/2009 3:35:25 PM #

Guru

Does this task support to watch a file over the FTP. If so how can I specify that in the task.

Guru United States

8/25/2009 8:05:28 PM #

Darren Green

The File Watcher Task does not support FTP.

Darren Green United Kingdom

8/26/2009 5:56:35 PM #

Steve

asked for some help back in April of '09 ... still don't see any ideas as to why we would have problems with the File Watcher Task... when we build a package for deployment and then install the package on another machine we get the error msg... "no path value has been specified" during validation steps. Import folder path is defined in the config, etc. So, we have thrown out the File Watcher Task and wrote our own to support a folder watch functionality... too bad the File Watcher Task doesn't allow for Deployment to another machine... ours does.

Steve United States

9/16/2009 5:20:29 AM #

Angela But

I am trying to use the filter in the File Watcher Task.  I have tried setting it to *_*.csv and also to *_ANZSIC.csv but the task gets all the files in my folder regardless of the name of the file.  What am I doing wrong?

Angela But Australia

9/16/2009 6:43:43 PM #

WD7179

Has anyone tried to incroporate this tool to use file modified time/date? I like the task, I just need to also look at modified time.

WD7179 United States

10/16/2009 6:39:51 PM #

Odalys

Thank you for making this available. I plan to use it in a pkg that will launch Excel autorun a macro that will produce a file, save it and then exit. The File Watcher will then catch the new file and upload to an ftp site. Cool!

Odalys United States

10/28/2009 8:29:39 AM #

Mohammed Mohsin

Hi,

Could you please let me know if you would be adding rename support to the file watcher?

Thanks,
-Mohsin

Mohammed Mohsin India

11/13/2009 5:52:26 PM #

STeve Tahmosh

Hi Alan,
I posted in June, and got re-assigned to another project, and didn't get notified of your helpful post.
You answered with helpful comments, but I didn't quite get one of them.
I apologize for missing your response.
My original question:
I had some issues with the file still being held once the FileWatcher finds it.
Not sure why the filewatcher releases when the file is still "held".
You answered that We raise 2 events on this task.  The first is to tell you we have seen the file being dropped and the second is to to tell you that you can go ahead and get a lock on the file.  
Are you trying to do your stuff in the right event?

That's a great suggestion to do my processing on the "got a lock event".
Can someone help me as to how to do that in a constraint?
Thanks,
STeve

STeve Tahmosh United States

11/13/2009 6:48:17 PM #

STeve Tahmosh

Further investigation.  I looked at the Event Handlers available, and found "OnFileFoundEvent"
There is also "OnFileWatcherEvent".  I found a definition for "OnFileWatcherEvent" as Advance warning, so this isn't what I am looking for.
Is the OnFileFoundEvent what fires after the lock is released?
Thanks,
STeve

STeve Tahmosh United States

11/30/2009 1:28:32 AM #

dave

this is great !!

dave

11/30/2009 1:28:47 AM #

nic

I love it too!

nic

12/24/2009 7:39:56 AM #

SALMANI RASHID

i am using the file watcher task which return the full outputfilename in a string variable(how to capture this)
and that output file name variable should be used as source variable for file file system task.but it shows "variable '@variablename' is used as source or destination and is empty"

SALMANI RASHID India

1/16/2010 1:39:34 PM #

Hems

having problems with the File Watcher Task... when we build a package for deployment and then install the package on another machine... "no path value has been specified" during validation steps... the configuration file has been edited to synch all respective values to the new machine... also, a package without File Watcher, when installed and config edited... works just fine.

please help  asap ,very stuck at this point...

Hems

2/8/2010 8:20:24 AM #

Ian

Hi, I use the file watcher extensively. works like a charm.
Can it be used to watch for 2 different file types. e.g. *.err, *.log?
If so, how?

Ian South Africa

2/8/2010 2:11:16 PM #

Darren Green

Ian, I'm afraid there is no way to specify multiple wildcard filter patterns at the same time, the best you could do would be to use *.* for all files (with an extension).

Darren Green United Kingdom

2/11/2010 4:07:26 PM #

padigem

Darren, are you aware of any performance issues with this task? I'm planning to use it across all my packages, so if I scheduled, say 10 packages, all waiting on various shared locations, how would it affect system resources? thanks

padigem United Kingdom

2/15/2010 10:14:11 AM #

Darren Green

Padigem, no issues that I know about. I think 10 would be fine, but having 10 SSIS packages regardless of function will require some overhead. It depends of course.

Darren Green United Kingdom

2/22/2010 8:21:40 PM #

Josef Richberg

I need to search for a file and I am trying to use windows wildcards.  The file will be <name>_mmddyy.xls or <name>_update_mmddyy.xls.  When I try this filter test_??????.xls, it picks up test_update_021210.xls, which it shouldn't.  How do I properly use wildcards?

Josef Richberg United States

3/2/2010 1:20:11 AM #

Jack L. Ricketts

I'm curious as to how much resource is being used if I have several File Watcher Tasks running.  Is it something I want kickoff and leave running or is it something I should put on a schedule?  Right now I'm only monitoring 10 directories for specific file types then the File Watcher Tasks trigger other DTS packages to move files or import into SQL Server.

Should I dedicate an entire small server to do SSIS Server work and use the File Watcher Task?

Jack L. Ricketts United States

3/3/2010 2:14:36 PM #

Aneyshia Taylor

I am using the file watcher task on SSIS 2008.  The task is set to wait 120 seconds for the file to be placed on the server.  The file is generally received between 7 and 8 am.  However almost every morning my package fails at 8:00 am with an error that the file could not be detected.  When I look on the file server the file is there and will have been there for some time.  The files are small so there isn't a problem with the file being released.  When I re-run the package it runs with no problems.  Has anyone else had this problem?

Aneyshia Taylor United States

3/3/2010 3:30:31 PM #

Aneyshia Taylor

After doing some testing, if the file is not received within a about 5 - 10 minutes the task will not pick up the file.  The task shows that it is waiting but it does not pick up the file.  Essentially the file watcher appears to stop watching for the file.  
I have the following options set:

Filter:  File.*.txt  (filename is file.today'sdate.txt)
Find Existing File:  True
Timeout Warning: False
Output Variable Name: User::FileFullPath
Timeout: 120
path Input Type :  I have tired Direct Input and variable
Path:  either my path or variable
Include Subdirectories:  False.

Aneyshia Taylor United States

3/8/2010 11:22:53 PM #

Bill

Just wanted to echo comments from [steve] in 4/09 & 8/09 - having a similar issue where a package with FileWatcher runs fine in one environment, but reports a validation error of [path not specified] if copied AS IS to another environment, when the path is hard-coded and valid. Someone with wisdom needs to comment about this.

Bill United States

3/29/2010 1:48:37 PM #

Hussein

Darren,
Thank you so much, works like a charm, does the job.  I have a question, how can the task return the size or any information about the file that was found?


Thanks
god bless anybody who shares the knowledge

Hussein United States

4/9/2010 11:55:15 AM #

Peter Brennen

Hey Darren,

Brilliant task, just what I need. I wondered if you know the best way to keep a Package watching? I just want it to watch for the file, find and process, repeat. Would I have to involve SQLAgent to restart the package when it's finished after a file process?

Cheers

Peter, NHS Liverpool

Peter Brennen United Kingdom

7/12/2010 2:06:43 PM #

John Woods

I have created a package with a File Watcher Task and no other tasks. I can get this to run without any problems on my development machine but when I try to import the package onto my Win2k3/SQL2K8 servers I get the following error:
Exception from HRESULT: 0xC00010026 (Microsoft.SqlServer.DTSRunTimeWrap)

I get this error for both 32bit and 64bit OS and SQL servers

John Woods United Kingdom

7/15/2010 9:49:39 AM #

Darren

John, I'm afraid that error doesn't mean anything to me, are you sure there is no more information available? Checking the obvious, you have installed the task on your Win2K3 server(s)?

Darren United Kingdom

8/2/2010 3:12:04 PM #

gaurav

Hi Guys,
I have SQL SERVER 2008 R2, i have installed SQL Server 2008 File watcher version but when i click on the varible property it throws me following error,
Could not load file or assembly 'Microsft.dataTransformationService.design, version = 9.0.242.0,culture =neutral
publicKeyToken = 89845dcd8080cc91' or one of its dependencies. the located assembly's mainfest defination does not match the assembly reference

I have installed the DLL pack mention in above comments but it is still not working for me.
Please let me know what to do
many thanks in advance..

gaurav United States

8/10/2010 10:52:44 PM #

Darren

gaurav, can you double check you have downloaded and installed version 2.0.0.14 of the task. The error sounds like a known bug with the old 2.0.0.7 release.

Darren United Kingdom

9/10/2010 12:58:28 AM #

Ronnie

You saved me so much time and a huge headache! I don't know why something like this doesn't come standard in BIDS.

Thanks!

Ronnie United States

10/4/2010 5:59:15 PM #

Mariano

I have SQL SERVER 2008, i have installed SQL Server 2008 File watcher version (2.0.0.14) but when i click on the varible property it throws me following error,
Could not load file or assembly 'Microsft.dataTransformationService.design, version = 9.0.242.0,culture =neutral
publicKeyToken = 89845dcd8080cc91' or one of its dependencies. the located assembly's mainfest defination does not match the assembly reference.

Please Help!
Thanks!

Mariano Bolivarian Republic of Venezuela

10/14/2010 2:50:50 PM #

Martin

Same kind of error that has been noticed before: FileWatcher task gives error when using a network directory


Microsoft (R) SQL Server Execute Package Utility  Version 10.0.2531.0 for 32-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    
Started:  15:44:51  Error: 2010-10-14 15:45:01.55     Code: 0x00000000     Source: File Watcher Task FileWatcherTask    
Description: The Path specified is not a valid directory. Please check directory exists and has appropriate security permissions (\\doccenter\Customers\VC\Exports\Articleextensions).  End Error  
Error: 2010-10-14 15:45:01.55     Code: 0xC0024107     Source: File Watcher Task      Description: There were errors during task validation.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  15:44:51  Finished: 15:45:01  Elapsed:  10.312 seconds.  The package execution failed.  The step failed.

The directory exists!

This error occurs when running the SSIS-Package from a job.
When running the package directly from the package store, then it works like expected.

What makes it different when running from a job (directly, not running using scheduler) compared to running directly form the package store, using exactly the same settings?

When using same FileWatcher task using a local drive, then it also works like expected (also when running from a job)

Martin Belgium

10/20/2010 9:36:44 AM #

Darren Green

Martin, there are two key points when running as a job compared vs interactively. The location may have changed, when scheduled in runs on the SQL Server machine, when run interactively it runs on the same machine as the SSMS/BIDS/VS tool being used. The security account may have changed, when scheduled in runs under an account determined by the SQL Server Agent (the service account or a specified proxy account), when run interactively it runs under the account of the logged on user who is running the SSMS/BIDS/VS tool being used.

Darren Green United Kingdom

11/1/2010 8:49:40 PM #

Joel

I am having the same problem that Kevin was having (see posts from 1/22/2009 and 1/27/2009).  Was there any resolution to this?

Joel United States

11/9/2010 1:45:10 PM #

Darren Green

Joel, as I replied to your direct email, are you sure the task is not just waiting for the file to become available? I never heard back.

As soon as a file is detected the tasks waits for that file to be available. We check file lock access to see if the file is available. This "delay" between the file being first detected and actually being available is often seen in FTP or WAN scenarios, when the file write or copy takes a long time. The tasks waits for the first file it sees, not the first file to become available, which may lead to some confusion.

Advance warning of a file having been detected, but not yet available is returned through the OnFileWatcherEvent. The OnFileFoundEvent is delayed until the file is available and ready for use. The OnFileWatcherEvent 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 detected created or changes, any subsequent changes will be ignored.

Darren Green United Kingdom

12/1/2010 11:29:25 PM #

Igor

I put File Watcher Task into Foreach Loop Container to process all existing files in folder. The container and FWT execute as many loops as number of files in the folder but every time FWT catches the same first file. Does it mean that I need to move processed file to another folder after loop execution? …or did I just miss something?
Thank you.

Igor Canada

12/2/2010 5:10:47 PM #

PeteCG

Hi, the task has worked fine on local machine, but after uploading to package store on server, it no longer picks up files. No error messages. .msi has been installed on server and service restarted. Any ideas?

PeteCG United Kingdom

12/6/2010 9:19:23 AM #

Darren Green

Igor, you will need to move the file. When the task executes inside a loop each execution is distinct, it cannot rememeber what happened last execution.

Pete, could there be something holding the file open? Is this a local folder?

Darren Green United Kingdom

1/21/2011 10:27:24 PM #

Peter

question: is the latest SQL Server 2008 version compatable with Server OS 2008 R2?
We are planning an upgrade and will test, but I'm researching now. Thanks.
-Peter

Peter United States

2/2/2011 7:37:52 PM #

Toby Rogers

Besides using zero (0) for unlimited timeout, is there a max number of minutes that can be put in the Timeout Option?

Toby Rogers United States

2/2/2011 8:13:33 PM #

Toby Rogers

How can I have the task move to another task on the failure, file does not exist.  I already have a task created that will send out an email that the file doesn't exist, but I can't get the File Watcher Task to launch it.

Toby Rogers United States

2/21/2011 10:04:57 AM #

Darren

Toby, the maximum timeout would be 2,147,483,647 minutes. I’m not 100% clear I understand your second request. If the task fails and you want to route control to a specific task, you can use a precedence constraint. Drag the green arrow from the task to the next task then double click the arrow to open the options dialog where you can change the type to failure.

Darren United Kingdom

2/21/2011 10:11:36 AM #

Darren

Peter, all our tasks/components are compatible with 2008 R2. There are no changes made or required between 2008 and 2008 R2 for SSIS.

Darren United Kingdom

3/10/2011 5:20:56 AM #

Robert F. Sonders

Darren.
First. Thank you for providing the FWT.  Nice functionality.
Quick question.  
I am using your task to check for the existence of a file.
Timeout = 1
Timeout Warning = False
If the file is there, great, pass the success constraint and move on within the Control Flow.  Where I am have an issue is the non-existence of a file.  If the file does not exist after one minute, the task fails. Which is fine.  I have a failure constraint from the FWT to a Send Email Task.  But, the Send Mail Task never executes.  Meaning the failure constraint never evaluates.

Any help is appreciated.
Thank you

Robert F. Sonders United States

3/18/2011 5:36:30 PM #

Anil

Darren,
Much thanks for this awesome task.

Second, i have a case where I watch for a file, for a minute. If the file exists or comes in that time frame, I pull some archived data. If it doesn't, I do nothing, but I also do not want the package to error.
Is it possible to do this? I experimented with the Timeout Warning, but that doesn't help, as setting it to True just makes the precedence constraint act as a success when the file is missing, and it still pulls the archived data. What I would want there at that point is for it to do nothing.

Thanks!
Anil

Anil United States

3/18/2011 9:04:18 PM #

Anil

Darren,
I figured out a way. Please ignore my earlier question.
Thanks,
Anil

Anil United States

5/6/2011 5:36:57 PM #

Correna Sergent

I'm having the same issue as Aneyshia.  My process doesn't run over the weekend so no file appears.  The job is scheduled to run daily (I don't see an option for specifying only certain days of the week to run).  When it doesn't find the trigger file over the weekend it just hangs.  I would like it to exit and try again at the next execution in this case.  For the other days of the week, I want to wait for the trigger file to ensure I'm processing the complete file that has been copied over to the server.  Any suggestions?

thanks,
Correna

Correna Sergent United States

6/15/2011 1:57:49 PM #

Greg

Hello, the File Watcher task recognizes the file and succeeds only if the file already exists at the monitored location.  However, if the job starts and the file is then FTPd to the directory AFTER the task starts, it never picks it up - using file in the form of \\server\share\filename.txt



Greg United States

6/22/2011 6:19:52 AM #

Matt

Hi we have attempted to schedule the file watcher task on our proudction server and we are getting the below message

Executed as user: ######. Microsoft (R) SQL Server Execute Package Utility  Version 9.00.4035.00 for 32-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  3:16:01 PM  Error: 2011-06-22 15:16:32.12     Code: 0x00000000     Source: Check for file with correct mask FileWatcherTask     Description: No Path value has been specified.  End Error  Error: 2011-06-22 15:16:32.12     Code: 0xC0024107     Source: Check for file with correct mask      Description: There were errors during task validation.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  3:16:01 PM  Finished: 3:16:32 PM  Elapsed:  30.609 seconds.  The package execution failed.  The step failed.

We are able to run this package successfully on the server without scheduling it?

Thanks

Matt Australia

6/30/2011 9:29:10 PM #

Stefan

I'm having the same issue as Greg. I'm getting a file via ftp and trying to pick it up with file watcher. But unfortunately, it never finds it - but would work well with a copy paste to the directory.

Stefan Switzerland

8/3/2011 11:37:44 PM #

Ken

I would like to know how this task can be scheduled or used - It works fine as long as I execute the task but when I load it up on the server - the ssis package is not running unless prompted.  I would like to know how to use this and execute to start the whole ssis process commencing with the source files this picks up?

Ken United States

10/21/2011 2:05:21 AM #

John

Hi, I am wanting to use FWT for a package I am creating. However, before I do so I need to ask a couple of questions that will be asked by my supervisor.

1. Does it need to be installed on the server where the SSIS package is going to run?
2. Can it be uninstalled if there are any problems with it?

Hope you can help.

Thanks
John

John Australia

11/24/2011 1:36:48 AM #

jfusco

i have set a FWT
path Input Type = Variable
path variable = user::PackageScopeVariableName_str

user::PackageScopeVariableName_str is in scope, type string and value = c:\myfolder

however the FWT errors with fails [FileWatcherTask] Error: The Path specified is not a valid directory. Please check directory exists and has appropriate security permissions ().
Permissions have be set to full for all users... and this is a valid directory..(if i hard code it it works) any assistance would be appreciated. thanks

jfusco United States

12/20/2011 5:55:55 PM #

Tanessa Richardson

We need to monitor for every change to a file that permanently resides in a folder (never gets dropped/deleted/renamed) and kick off a package every time a change is detected.  Can we do this with the File Watcher Task?

Tanessa Richardson United States

1/20/2012 9:41:40 AM #

Nigel

I have been using the File Watcher task (great component, btw, I have used it on a number of projects) in a loop, watching a file share for XML files, processing them as they arrive and moving them to another folder when done.

In this particular project, it has been working without any problems for 6-7 weeks now. A couple of days ago I had to restart the SQL Agent job I use to run the package and all seemed fine. I have just found, however, that after just over 24 hours, the component just stopped noticing files as they arrived in the share. Restarting the SQL Agent job restarted file processing.

Apparently, the server hosting the file share was rebooted at about the time that this stopped working. Is it possible that the absence of the file share has stopped the component from working and is there a way to handle this other than to remember to restart the job if the share is rebooted?

Nigel United Kingdom

2/3/2012 2:54:08 AM #

Charlie

Is there a way to have File Watcher present the oldest file in the directory first?

I would like to make sure the oldest files get processed first.

Charlie United States

4/20/2012 11:30:10 PM #

Andy

Is there a version of this component available for SSIS 2012.

When I upgraded my SSIS packages to SSIS 2012, the upgrade failed... The error message indicates an issue with Konesans File watcher task.

Andy United States

5/2/2012 4:44:00 PM #

Anil

May be I should be the first to ask it here.

Is the File Watcher Task compatible with SQL Server 2012?

So far, I have not been successful in adding (or even finding) the task in Choose Control Task window.

Anil United States

5/10/2012 5:08:25 PM #

Nikita Menon

Hello ,

  I'm new to SSIS,I was asked to upgrade an SSIS built by my predecessor,he used the File Watcher Task in all of the packages. Now when I try to upgrade it gets an error with the File Watcher as I cannot change the path to look for files. All other components I was able to edit and save it properly,when I try to edit this task its all in expressions and variables .I'm using SQL 2005 . Could you please tell me how to Edit this task .

Regards,
Nikita

Nikita Menon United Kingdom

6/1/2012 1:35:39 PM #

Orlando

Great component! Will a SSIS 2012 version be released at some point? Will the source code be made available?

Orlando United States

6/6/2012 7:43:01 AM #

Darren Green

The SQL 2012 version is now available, sorry for the delay. I don't plan to release the source code.

Darren Green United Kingdom

6/6/2012 2:40:23 PM #

Orlando

> The SQL 2012 version is now available, sorry for the delay.

Thank you very much!

> I don't plan to release the source code.

May I ask why not? This would be a great addition to CodePlex. I see some comments on this board asking for features that the community could produce and contribute to this already great tool.

Orlando United States

6/6/2012 3:39:01 PM #

Anil

Darren, you rock - thanks for the 2012 version.

Anil United States

6/11/2012 10:59:57 AM #

Derek Robinson

I was looking to assess this component for inclusion in my development projects but I'm always wary with the "Free to good home" types. Seems there is a scheduling problem that's needed attention for some months and I don't see any acknowledgement of it by the author. Lack of access to source code is an issue too. How do comercial companies know that there's not something nasty lurking in there after we've given it access to our data? So it falls at the first fence for our auditors who demand transparency in such things.
I appreciate you worry about IP but there are seriously large software houses out there that provide their source code for just such reasons.

Derek Robinson United Kingdom

6/12/2012 10:47:17 PM #

Darren Green

Nigel, whilst I haven’t tested it explicitly it seems reasonable that the underlying Windows API loses the ability monitor the file share after the loss of connection. The component uses the . NET File system watcher object which itself is a wrapper around the Windows API. Personally I would always worry about a continuously running process that which is event driven, and unfortunately your case illustrates why. I would suggest that by some defensive design you can however work around this quite well. For this type of thing I added the TimeoutAsWarning (and FindExistingFiles) properties. This means you can just expire the running package without a failure. Using a SQL Agent job set to start the package on a regular frequency means you should soon be up and running again. You can ask SQL Agent to run the package every minute, but set the timeout to several hours. SQL Agent can only start job when the current one has finished, it won’t try and start I multiple times. It is also common to host the task inside a Loop container. This can also be used to restart the task. Whilst often used to process multiple files one after the other, it also works for a faster timeout “cycle” of the task. I would use a combination of SQL Agent restarts and the loop, with timeout and job frequency depending on the frequency you expect files to arrive and the latency you can allow before they are processed.

Darren Green United Kingdom

6/12/2012 10:47:45 PM #

Darren Green

Orlando, to open source something I think the code needs to be suitable, and for something like this it needs to be standalone and easy to produce a compatible build. The task uses common code shared between many of my components, and that I think would make it difficult to maintain, as there is more code than you need for the File Watcher Task. It makes sense for me, because I have less code to maintain due to the shared nature, but that is trade off with managing the complexity of the inter-dependencies. When I released the Expression Tester (http://expressioneditor.codeplex.com/)  I took time to pull that part from my shared code, and make it confirm to common standards e.g. StyleCop, Code Analysis.  I would also not be willing to release the certificates used to sign the assembly (strong name) or MSI, so a compatible build is not possible. (You could probably work around the change of a string name via UpgradeMappings). The code also contains what I consider to be company IP, hence the copyright and licensing terms supplied with the components. Whilst I have previously made commitments to keep the components free, I also made it clear they would remain closed source, and at present I wish to keep it that way.

Darren Green United Kingdom

6/12/2012 11:37:12 PM #

Darren Green

Derek, I think you raise fair questions. I too consider carefully before using free software, and have worked in environments where it is effectively prevented for a variety of reasons, including being closed source, but I still think what I do offers some benefit for many who can. The scheduling problem is as per my very recent reply to Nigel, an issue with the Windows API, just search online for ".NET file system watcher network share” and you’ll see it is not confined to my usage of the class. My timeliness of responses unfortunately is a poor reflection on me, and I won’t try and make any poor excuses.

Whilst IP is only one reason, the case of seriously large software houses with seriously large resources would probably deter IP theft, not to mention them having resources to pursue any action. I don’t wish to make a case for close vs open software IO am happy with both models, so please don’t take my responses that way. I was just trying to address the yours. Many organisations offer closed source software, and I’m sure you use some, so why that over mine? Trust? Scale of company? I think they are bigger risks to evaluate.

If I offered your auditors access to my source code would they seriously take it up? I suspect they wouldn’t take the time to review the code at all, it would cost too much. Do they review existing open source code on a case by case basis, or just assume that someone else has and would have flagged it to the wider open source community? I’d be genuinely interested to know what they really do.

As an aside I have been contacted directly in the past by for this type of issue, including access to source code, commercial terms, support agreements etc. Each time I have asked for specifics of what would take to satisfy the organisations requirements and each time the responses have quickly fizzled out.

For now though I hope you can accept that I wish to keep the components as-is, closed source with the best support I can offer for free.

Darren Green United Kingdom

6/17/2012 12:04:58 AM #

Orlando

Thank you Darren, for your responses (not just to me) and for producing the SQL 2012 version. What you have provided is of great service to the community.

Switching gears a bit, I installed version 3.0.0.16 on a clean machine. So, my clean environment is as follows:

- Windows 7 Ultimate x86 w/ SP1 + all patches available from Windows Update
- SQL Server 2012 Evaluation Edition (only DB Engine, Mgmt Tools Complete, IS and SSDT selected during install)
- File Watcher Task 3.0.0.16
- ACE OLE DB Driver 12.0 (Microsoft Access Database Engine 2010 Redistributable)
- Microsoft Security Essentials
- nothing else

All was going well until I tried to set the "Output Variable Name" value in the Task Editor using the drop-down arrow and I received this:

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. (Exception from HRESULT: 0x80131040)

I tried to work around the issue by creating a String variable named User::FileNameVariable and setting its value to my desired output variable name, User::DropFile; then setting the value of the "Output Variable Name" in the Task Editor to an Expression containing "User::FileNameVariable". The attempt at misdirection failed too. While the Task Editor validated and allowed me to save and run the package, at runtime it was not setting the value of User::DropFile as I wished. Do you think there is something missing or misconfigured in my environment or do you think the component needs to be modified?

Orlando United States

6/18/2012 8:31:44 PM #

Darren Green

Orlando, that is a bad reference on my part, please bear with me. You can work around the issue by typing in the variable name rather than using the drop-down, e.g. "User::Variable1". (Your comment post wasn't lost, but comments are semi-auto moderated, and yours got flagged for manual approval.)

Darren Green United Kingdom

6/19/2012 9:49:17 AM #

Darren Green

New release of the File Watcher Task for SQL Server 2012 now available, 3.0.0.20. This corrects the bug with the "Output Variable Name" feature as reported by Orlando above.

Orlando, thanks for the detailed report, and excuse me not reading the last part about the failed work around properly.

Darren Green United Kingdom

6/19/2012 12:24:57 PM #

Orlando

I can now set the value of the Output Variable Name in the task editor using the drop-down list. As a side note, my workaround (and yours of setting the value directly without using the drop-down) may have worked if I had not had a bug in my code preventing my variable User::DropFile from being properly set by the File Watcher Task. At any rate, I am using the newer version and the drop-down works great. Thanks for rolling out the new version!

Orlando United States

Add comment

  Country flag

biuquote
  • Comment
  • Preview
Loading

Widget Amazon Book not found.

Object reference not set to an instance of an object.X

RecentComments

Comment RSS