Easy and efficient way to log overwriting of a directory in SQL Server Integration Services (SSIS)

Recently I had the problem that I had a File System Task that moved a file but whenever the file was already there the package failed. So I set OverwriteDestination to TRUE. But now I lost complete control over which files were just moved and which did overwrite some already existing directory. My desired result was to get a message in my log file that some directory was overwritten by the FileSystemTask.

Set OverwriteDestination=TRUE
Set OverwriteDestination=TRUE

Google is not always your friend

My first attempt was to google for a solution, but the only suggestions it came up with was how to overwrite a log file and not how to log that a file was overwritten.

Unsuccessful Google attempts.
Unsuccessful Google attempts.

Use event handlers

Since I recently started working with Event Handlers I had the idea I could use one for this task. In general you could create a Script Task as part of the Control Flow (as we will create a Script Task anyway) but this is a much cleaner solution in my opinion. This small task is not a part of the Control Flow but rather really something that should be part of the PreExecution phase of the FileSystemTask.

To create an event handler:

  1. Move to the tab Event Handlers
  2. Select the executable (File System Task) and the event handler (OnPreExecute)

<rant>This tab is really not neatly arranged, why can’t there be a list of all event handlers somewhere?<\rant>

For the logic create a Script Task.

Create a PreExecute EventHandler for your FileSystemTask.
Create a PreExecute EventHandler for your FileSystemTask.

Let a script task do the work

By double clicking on the script task you get to its edit windows. Choose the variables you want to pass to the C# script, in my case:

  • FolderDestination
  • LogFile_ConnectionString
  • PackageName
Chose the variables you want to pass to the script.
Chose the variables you want to pass to the script.

Click “Edit script…” and add this code (and adapt it to your needs) to the main function of your ScriptMain.cs.

Final words

Events are pretty useful to do such simple tasks and I have to admit that I should use them much more. It’s a pity that they are not more prominent (e.g. it could be displayed in the Control Flow that a certain task has an event handler).

Leave a Reply

Your email address will not be published. Required fields are marked *