Using the Script Task in SSIS to Process Data Files When They Arrive
-
RonKyle (1/17/2013)
The Script Task brings with it some flexibility and ease of use that offer us some advantages over the other two techniques, yet has some potential drawbacks of its own.
This is from the last paragraph, but looking through the table there didn't seem to be an obvious drawback. Did you mean in general or just depending on the environment? Could you give a specific drawback?
Thanks,
The potential drawbacks I think I had in mind when I wrote that were mostly related to the politics surrounding what kind of code some decision-makers want to support in their shops. C# is typically not a skill most SQL Server Professionals want, or in some cases even care, to learn. As such a solution like this might not be too welcome in some shops.
I tried to provide a solution where someone with little or no C# development experience could begin to leverage it. If someone without much C# experience implemented the package and there was a problem that traced back to the Script Task, or someone wanted to extend the functionality to do something additional or in a different way, then that would be a completely different story.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
Thanks for sharing your article. I need to try implement something similar. So I'll be "stealing" bits and pieces from your code.
With regards to your C# statement, I'm a SQL pro, and I've been putting off learning C# for a few years now... I guess there's just so much to do and learn with SQL, SSIS, SSAS, data and the day job - mine involves Corporate Bank Risk model development and the related systems and data streams - not something that will get much benefit of my learning C#. However so tasks such as staging data automatically are best performed with a bit of C# interjection and with proper documentation shouldn't cause much consternation from future analysts, developers or DBAs.
Ok, I'm sold, now where'd I put that book Learn C# in 24 hours?
Michael Gilchrist
Database Specialist
There are 10 types of people in the world, those who understand binary and those that don't. 😀 -
If you are having difficulty getting this to work consistently, try adding the following to WatchForFileCreation:
// enable the watcher
fileSystemWatcher.EnableRaisingEvents = true;
just prior to the WaitForChanged call. In the .NET 2.0 framework (which is what most SSIS folks use) this defaults to false.
-
Dave Pendleton (3/28/2013)
If you are having difficulty getting this to work consistently, try adding the following to WatchForFileCreation:// enable the watcher
fileSystemWatcher.EnableRaisingEvents = true;
just prior to the WaitForChanged call. In the .NET 2.0 framework (which is what most SSIS folks use) this defaults to false.
Thanks for posting this. Are you on SSIS 2005, SSIS 2008 or SSIS 2008 R2? As I understand it both SSIS 2005 and SSIS 2008 R2 use .NET Framework 2.0 to support the Script Task and SSIS 2012 uses .NET Framework 4.0. The article was written for and tested with SSIS 2012 but I understand that most SSIS production code is still running on SSIS 2008 R2 or earlier, so after some requests from some folks I backported the code from the article to SSIS 2008 R2 and my unit tests came back OK. You'll find that code somewhere earlier in these comments.
It is true that the property EnableRaisingEvents is false by default but according to the documentation (and trust me, I know Microsoft's docs can be inaccurate or ambiguous, that has bitten me multiple times) it should not matter as the invocation of the WaitForChanged method means you do not need to explicitly set EnableRaisedEvents to true. From the article FileSystemWatcher.WaitForChanged Method (WatcherChangeTypes, Int32) - .NET Framework 2.0 under Remarks (underline added):
This method allows an event handler to be invoked to respond to file changes even if the EnableRaisingEvents property is set to false.
I am curious to recreate your issue because it means there could be a bug in the Framework, or it could mean the documentation is inaccurate. Would you mind sharing which version of SSIS you are on and also which version of the .NET Framework you have installed on your machine? Did you happen to modify the script code at all from what was in the article, over and above the one change you showed? Either way, could you post the code from the Script Task in its entirety?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
I have all the .NET frameworks installed, but I only use 2.0 in SSIS 2008.
I am monitoring a folder for excel files. The folder being monitored is a share on different server than the one hosting the package. The excel files have long file names with spaces. They are being created by the reporting functionality of our clinic software.
The server hosting the package is running Windows Server 2008 R2. The LZ is also WS 2008 R2.
This all only worked intermittently, and then not at all. Once I explicitly set the value of EnableRaisingEvents, it began working again. At first, I thought it might be the way in which the file was created. When I debugged my package on my laptop, I would manually copy the file to the drop folder (a local folder) and it would work. It would also sometimes work when I manually copied the file to the network URI. I didn't spend enough time debugging this aspect of the problem because my users want everything yesterday. As of this afternoon, I was able to get it working by setting the property value and creating the file via our clinic software.
I will have more time to play around with this next week. I'd like to be able to replicate this in a verifiable way because yes, it does seem like a bug.
I'll post the code later; my laptop is at work.
-
Here is the code I'm using.
using System;using System.IO;
using System.Threading;
namespace HEC
{
[System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
#region VSTA generated code
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion
// we need access to the found file info from the FileSystemWatcher OnFileCreate event in our class
// scope. An instance variable may look odd but will do for our purposes
private FileInfo foundFile = null;
/// <summary>
/// This method is called when this script task executes in the control flow.
/// Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
/// To open Help, press F1.
/// </summary>
public void Main()
{
try
{
// initialize common variables from DTS variables collection
string dropDirectory = Dts.Variables["User::WatcherInputDropPath"].Value.ToString();
string fileMask = Dts.Variables["User::WatcherInputFileMask"].Value.ToString();
bool includeSubdirectories = Convert.ToBoolean(Dts.Variables["User::WatcherInputIncludeSubdirectories"].Value);
// look for existing files if configuration suggests we should
bool findExistingFiles = Convert.ToBoolean(Dts.Variables["User::WatcherInputFindExistingFiles"].Value);
if (findExistingFiles)
{
FindExistingFile(dropDirectory, fileMask, includeSubdirectories);
}
// do we (still) need to look for a file?
if (foundFile == null)
{
// if we made it here there were no existing files to process (or we didn't check for them per the
// configuration variables) so setup a FileSystemWatcher object per the configuration variables
bool timeoutAsWarning = Convert.ToBoolean(Dts.Variables["User::WatcherInputTimeoutAsWarning"].Value);
bool fireAgain = true;
int timeoutSeconds = Convert.ToInt32(Dts.Variables["User::WatcherInputTimeoutSeconds"].Value);
int timeoutMilliseconds = (timeoutSeconds == 0 ? -1 : timeoutSeconds * 1000);
string message = String.Format("Waiting for file (Path={0}; Filter={1}; IncludeSubdirectories={2})", dropDirectory, fileMask, includeSubdirectories.ToString());
Dts.Events.FireInformation(0, null, message, string.Empty, 0, ref fireAgain);
WatchForFileCreation(dropDirectory, fileMask, includeSubdirectories, timeoutAsWarning, timeoutMilliseconds);
}
Dts.TaskResult = (int)ScriptResults.Success;
}
catch (Exception e)
{
Dts.Events.FireError(0, null, e.Message, string.Empty, 0);
Dts.TaskResult = (int)ScriptResults.Failure;
}
}
/// <summary>
/// Event attached to FileSystemWatcher when a file is created.
/// </summary>
/// <param name="source">Event source.</param>
/// <param name="e">Event arguments.</param>
private void OnFileCreate(object source, FileSystemEventArgs e)
{
PreProcessFoundFile(new FileInfo(e.FullPath));
}
/// <summary>
/// Sets up a FileSystemWatcher to watch for new files being created.
/// </summary>
/// <param name="dropDirectory">Directory to watch</param>
/// <param name="fileMask">File pattern mask of files being watched for.</param>
/// <param name="includeSubdirectories">If true all subdirectories are also watched.</param>
/// <param name="timeoutAsWarning">If true then if watcher times out only a warning is raised, i.e. the Task succeeds.</param>
/// <param name="timeoutMilliseconds">Number of milliseconds to wait for a file to be initially created. This timeout period
/// does not apply to the tiem spent waiting for exclusive access to be gained to the file.</param>
private void WatchForFileCreation(string dropDirectory, string fileMask, bool includeSubdirectories, bool timeoutAsWarning, int timeoutMilliseconds)
{
// create a new FileSystemWatcher
FileSystemWatcher fileSystemWatcher = new FileSystemWatcher();
// set the path to watch to our 'drop directory'
fileSystemWatcher.Path = dropDirectory;
// set the option to watch subdirectories
fileSystemWatcher.IncludeSubdirectories = includeSubdirectories;
// set the filter of files to watch for to our 'file mask'
fileSystemWatcher.Filter = fileMask;
// add event handler to execute when new files are created
fileSystemWatcher.Created += new FileSystemEventHandler(OnFileCreate);
// enable the watcher
fileSystemWatcher.EnableRaisingEvents = true;
// begin watching
fileSystemWatcher.WaitForChanged(WatcherChangeTypes.Created, timeoutMilliseconds);
if (foundFile == null)
{
// the file watcher timed out waiting for a file :-<
string message = String.Format("Timeout waiting for file (Path={0}; Filter={1}; IncludeSubdirectories={2})", dropDirectory, fileMask, includeSubdirectories.ToString());
if (timeoutAsWarning)
{
// only raise a warning
Dts.Events.FireWarning(0, null, message, string.Empty, 0);
}
else
{
// raise an error
throw new TimeoutException(message);
}
}
}
/// <summary>
/// Takes actions subsequent to locating a file that allow later processing of the file. This method
/// reports information to the parent container by firing info events. This method also ensures exclusive
/// access to the file can be achieved before returning control to the parent container.
/// </summary>
/// <param name="dataFile">File to preprocess.</param>
private void PreProcessFoundFile(FileInfo dataFile)
{
// set the instance variable value to the found file
this.foundFile = dataFile;
// local variable to pass to events that require parameters be passed by ref
bool fireAgain = true;
// raise an information event saying we found a file (not necessarily that it can be used)
Dts.Events.FireInformation(0, null, "File found: " + dataFile.FullName, string.Empty, 0, ref fireAgain);
// We know there is a new file that can be processed because the FileSystemWatcher fired
// an event, however we do not know if the user or process supplying the file has completed
// uploading it. We will loop over drop directory looking for files that meet our criteria
// and once we find one we will make sure the supplier has completed their upload process by
// checking to see if we can gain exclusive access to the file. Once we can gain exclusive
// access to the file we will know the upload is complete and we can allow the rest of the
// SSIS package to continue.
WaitForExclusiveAccess(dataFile);
string newFileName = Path.GetFileNameWithoutExtension(dataFile.Name);
// store the full file name (includes path) in output variable
Dts.Variables["User::WatcherOutputFileFullName"].Value = dataFile.FullName;
// store the file name in output variable. This is used as the expression for User::ArchiveFileName
Dts.Variables["User::WatcherOutputFileName"].Value = Path.GetFileNameWithoutExtension(dataFile.Name);
// raise an information event saying we found a file -and- it can be used
Dts.Events.FireInformation(0, null, "File ready for use: " + dataFile.FullName, string.Empty, 0, ref fireAgain);
}
/// <summary>
/// Waits until exclusive access to a file can be achieved.
/// </summary>
/// <param name="dataFile">File to access.</param>
private void WaitForExclusiveAccess(FileInfo dataFile)
{
// local variable to say how many seconds to wait in between checking if we can gain
// exclusive access to the found file
int secondsToWaitBetweenAttempts = 5;
// local variable to pass to events that require parameters be passed by ref
bool fireAgain = true;
// Loop indefinitely checking if we can access the data file.
while (1 == 1)
{
try
{
// Attempt to gain access to the file.
using (Stream stream = new FileStream(dataFile.FullName, FileMode.Open))
{
// If we made it here no exception was thrown meaning we
// could access the file. We will break out of the loop and allow
// the rest of the package to continue processing.
break;
}
}
catch (IOException)
{
// We are not interested in ending the program when an IOException
// occurs in this area. This type of exception means we could not
// gain access to the file.
// In general, programming algorithms that leverage exceptions for
// control flow are frowned upon. However in the case of file access
// it is an acceptable pattern.
}
string message = "Could not gain exclusive access to file {0}. Waiting {1} seconds before trying again...";
// raise an information event saying we could not gain exclusive access to the found file and will wait
Dts.Events.FireInformation(0, null, String.Format(message, foundFile.FullName, secondsToWaitBetweenAttempts.ToString()), string.Empty, 0, ref fireAgain);
// wait some time before checking whether the file can be used
Thread.Sleep(secondsToWaitBetweenAttempts * 1000);
}
}
/// <summary>
/// Check a directory for files that match a file mask.
/// </summary>
/// <param name="directoryName">Directory to look for files.</param>
/// <param name="fileMask">File pattern mask matching files to look for.</param>
/// <param name="includeSubdirectories">True if subdirectories should also be checked.</param>
private void FindExistingFile(string directoryName, string fileMask, bool includeSubdirectories)
{
// get the list of files that qualify
DirectoryInfo directoryInfo = new DirectoryInfo(directoryName);
FileInfo[] fileInfos;
// local variable to pass to events that require parameters be passed by ref
bool fireAgain = true;
if (includeSubdirectories)
{
fileInfos = directoryInfo.GetFiles(fileMask, SearchOption.AllDirectories);
}
else
{
fileInfos = directoryInfo.GetFiles(fileMask, SearchOption.TopDirectoryOnly);
}
// check to see if any files were found
if (fileInfos.Length > 0)
{
// found a file!
PreProcessFoundFile(fileInfos[0]);
// raise an info message
Dts.Events.FireInformation(0, null, "Existing files found: " + fileInfos.Length.ToString(), string.Empty, 0, ref fireAgain);
}
else
{
// no files found, raise a warning
Dts.Events.FireWarning(0, null, "No existing files found.", string.Empty, 0);
}
}
}
}
-
I guess I spoke too soon; it isn't working as desired right now.
If I copy the file "manually" to the drop folder it will work every time. When the file is created "automatically" by our clinic software (run as a report). The File Watcher does not see the file creation and nothing happens.
-
Dave Pendleton (4/1/2013)
I guess I spoke too soon; it isn't working as desired right now.If I copy the file "manually" to the drop folder it will work every time. When the file is created "automatically" by our clinic software (run as a report). The File Watcher does not see the file creation and nothing happens.
Interesting...the enum that dictates which events are "watched" for is defined here:
WatcherChangeTypes Enumeration - .NET Framework 4
The code uses Created and as you can see there aren't many alternatives that might qualify for your scenario.
Unless there is a hole in how the FileSystemWatcher class is implemented within the .NET Framework then your file should be detected and the event should be fired. YOu should be able to tell if you;re logging output because I am raising an Information message in that case.
If your client program is never closing the open file handle after it creates the file then the code will loop, checking for exclusive access. See the WaitForExclusiveAccess method in the Script Task. If that scenario is occurring then Informational messages will be fired each time it tries and fails to gain access which you would also see if you're logging output:
Could not gain exclusive access to file foundFileName. Waiting n seconds before trying again...
It will stay in this loop indefinitely if the program that created the file never closes the open file handle, or something else opens it before the SSIS can gain access, because the program can only assume the file is still being written to. Let me know if that is what you are experiencing.
One poster already pointed out that it would be nice to have the program timeout in cases where it is waiting for exclusive access, and I agree, that would be a nice addition: http://qa.sqlservercentral.com/Forums/FindPost1402917.aspx
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
I'm still working out some kinks, but as for SQL 2008 R2 compatibility, all I had to do was replace:
[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
with:
[System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
for it to compile in BIDS 2008 R2.
-
Dave Pendleton (3/28/2013)
If you are having difficulty getting this to work consistently, try adding the following to WatchForFileCreation:// enable the watcher
fileSystemWatcher.EnableRaisingEvents = true;
just prior to the WaitForChanged call. In the .NET 2.0 framework (which is what most SSIS folks use) this defaults to false.
B R I L L I A N T ! ! ! ! ! ! ! ! ! !
Thanks so much, Grasshopper. In my case, the event was not being raised unless the first file added to the folder matched the criteria. What I mean is that I had two files in my source folder (hi.txt and hi.xlsx). I set the WatcherInputFileMask filter to *.xlsx. When I dropped hi.xlsx in the WatcherInputDropPath folder, everything worked. However, if I first dropped hi.txt there and subsequently dropped hi.xlsx there, the event did not fire. Also, if I dropped them both at the same time it wouldn't fire (consistently anyway).
I spent several hours troubleshooting this before I read your suggestion. Simply adding:
fileSystemWatcher.EnableRaisingEvents = true;
prior to the
fileSystemWatcher.Created += new FileSystemEventHandler(OnFileCreate);
statement made everything work perfectly. It will wait as multiple files that don't match the filter are added, and still raise the OnFileCreate event when a matching file is created.
-
I'm learning SSIS and I have been trying to use SSIS to move files from different source locations to different destination locations. The files in each source location follow a pattern.
Examples:
Move MyFile1 and MyFile2 from C:\TestFolder1\ to C:\TestFolder2Move OtherFile1 and OtherFile2 from C:\AnotherFolder1\ to C:\AnotherFolder2
These files come in at different times during the month so I'd like to use a continuous File Watcher that monitors the source locations and copies the files. However, I'd prefer not to have to create separate packages for each source location. I've created a database table that contains all of the source and destination paths and file patterns. I've used a FOREACH loop to iterate through the results from the database table and I've used a FOREACH loop to move the multiple files from a single source location to its proper destination location, but I'm not able to figure out how to incorporate a continuous File Watcher successfully.
Of your three approaches to process data files when they arrive, I do prefer using the Script Task.
Is there a way using this Script Task approach, to not error when the first source location is empty and move on to look in the second source location, etc. and when finished looking in the last source location, it would start looking in the first source location again?
I'm open to other solutions also.
I'm using Microsoft SQL Server 2008 R2.
Thanks!
-
SanPoke (9/3/2014)
I'm learning SSIS and I have been trying to use SSIS to move files from different source locations to different destination locations. The files in each source location follow a pattern.Examples:
Move MyFile1 and MyFile2 from C:\TestFolder1\ to C:\TestFolder2Move OtherFile1 and OtherFile2 from C:\AnotherFolder1\ to C:\AnotherFolder2
These files come in at different times during the month so I'd like to use a continuous File Watcher that monitors the source locations and copies the files. However, I'd prefer not to have to create separate packages for each source location. I've created a database table that contains all of the source and destination paths and file patterns. I've used a FOREACH loop to iterate through the results from the database table and I've used a FOREACH loop to move the multiple files from a single source location to its proper destination location, but I'm not able to figure out how to incorporate a continuous File Watcher successfully.
Of your three approaches to process data files when they arrive, I do prefer using the Script Task.
Is there a way using this Script Task approach, to not error when the first source location is empty and move on to look in the second source location, etc. and when finished looking in the last source location, it would start looking in the first source location again?
I'm open to other solutions also.
I'm using Microsoft SQL Server 2008 R2.
Thanks!
Setting the WatcherInputTimeoutAsWarning variable to True will allow the Script Task to only raise a warning if it times out waiting for a file to arrive, as opposed to it raising an error. A For Each Loop Container containing a watcher Script Task that iterates over a resultset, e.g. your source and destination paths and file mask, could work. The For Each Loop could map the resultset values into variables that are passed to the Script Task The Script Task would wait for an allotted time for a file to arrive in each directory, and if one did not arrive it would simply raise a warning and allow the loop to continue. You have several options to consider regarding whether to iterate over the resultset numerous times, potentially allowing the Package run continuously all month. You could even have the Package re-check the table after each iteration through the resultset once in case something was added or removed from the table. Note that if there are multiple files in each directory the Script Task would only process one file per iteration. Thanks for reading.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
Thank you for your reply.
I set the "WatcherInputTimeoutAsWarning" = True and set the "WatcherInputTimeoutSeconds" = 10.
I have a FOREACH Loop to iterate through my "DatafileInfo" table. That FOREACH Loop contains a FOREACH File Enumerator Loop around the Watcher Script Task and Move File System Task. Once all of the files have been moved from the first source location, I need a way to allow the Watcher Script Task to Timeout without failing, then have it skip the Move File System Task and go out to the FOREACH Loop for the "DatafileInfo" table to get the second source location. Currently, if I allow the Watcher Script Task to Timeout with a warning, then the process steps into the Move File System Task which errors because it is trying to move the last file it found which has already been moved.
Any help would be appreciated. Thanks!
-
SanPoke (9/5/2014)
Thank you for your reply.I set the "WatcherInputTimeoutAsWarning" = True and set the "WatcherInputTimeoutSeconds" = 10.
I have a FOREACH Loop to iterate through my "DatafileInfo" table. That FOREACH Loop contains a FOREACH File Enumerator Loop around the Watcher Script Task and Move File System Task. Once all of the files have been moved from the first source location, I need a way to allow the Watcher Script Task to Timeout without failing, then have it skip the Move File System Task and go out to the FOREACH Loop for the "DatafileInfo" table to get the second source location. Currently, if I allow the Watcher Script Task to Timeout with a warning, then the process steps into the Move File System Task which errors because it is trying to move the last file it found which has already been moved.
Any help would be appreciated. Thanks!
I was thinking of a few ways you could do this, but you might try modifying the precedence constraint between your Script Task your Move File Task to also check for a value in "WatcherOutputFileName". The code sample I provided is built to process a single file so if the Script Task exits without finding a file variable "WatcherOutputFileName" will be blank. However, because you are adding it into a loop you will need to modify the Script Task to initialize the variable to blank each time it begins, e.g. in Main(), so you do not get false-positives on a current iteration based on the last iteration having found a file. The precedence constraint between your Script Task your Move File Task might look like this:
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
opc.three (10/25/2012)
dave-dj (10/25/2012)
excellent and comprehensive article.I only have a couple of questions..
first - if you where required to process multiple files per day, would you adapt this process, or would you use a process similiar to the others whereby the package is run every 5 minutes (and subsequently loaded and unloaded from memory)?
If it were part of the requirement to process multiple files per day then you have some options. In keeping with the idea that we want our SSIS package to run continuosly, and not have to stop and start it every so often, I would look into putting the entire set of tasks into a For Loop with a condition of 'where 1=1'. This would effectively bring us back to the 'watcher' after processing a file. If there was a possibility for a file to arrive during the processing of a previous file, then you could consider setting WatcherInputFindExistingFiles to True to ensure that file were processed.
Thank you so much for taking the time to document this project - it has been incredibly helpful to me as I needed to do a very similar task - great stuff!
I have a situation where I need the watcher to be active fairly continually as files dropped in the drop folder could be dropped at any time, possibly multiple times a day. I Built all the tasks in the project and everything worked well, so I incorporated them all in a For Loop with a condition of 1 == 1. When I start the project and drop in a few files they process through without issue - everything works a planned and I can see the script task sitting there in yellow watching for new files. The problem is, after the project process a first file or group of files, subsequent files dropped into the drop directory do not get picked up and I don't know why - perhaps something needs to be reset?
Perhaps running in a loop isn't the best plan anyway - it looks like there could be some concerns with CPU usage. I could just call a non-looped package as a SQL Job Agent every few minutes, but unless I have the tasks in a loop it appears to only process 1 file at a time - if the system dropping files suddenly drops 50 files in the directory I'd need the Job Agent to restart 50 times to process through all of them - there's got to be a better way - any suggestions?
I should mention I'm using 2008R2 and used the updated script posted in this thread for my script task (thank you again for posting that!)
Viewing 15 posts - 31 through 45 (of 62 total)
You must be logged in to reply to this topic. Login to reply