Event Handlers in SSIS Control Flow

Event handlers are a powerful feature in SQL Server Integration Services (SSIS) that allow you to respond to specific events that occur during the execution of a package, task, or container. This enables you to implement custom logic for handling errors, successes, progress, and other significant moments in your data integration process.

Understanding SSIS Events

SSIS raises various events throughout the lifecycle of package execution. These events can be categorized as:

• Package-level events: Triggered by the overall package execution (e.g., OnPreExecute, OnPostExecute, OnError, OnWarning, OnInformation, OnProgress).
• Task and Container-level events: Triggered by individual tasks (e.g., Data Flow Task, Execute SQL Task) or containers (e.g., Sequence Container, For Loop Container) within the control flow.

When an event is raised, SSIS can execute a pre-defined event handler associated with that event. This handler is itself an SSIS package component containing one or more executable tasks.

Creating an Event Handler

To create an event handler, you typically work within the SSIS Designer in SQL Server Data Tools (SSDT) or Visual Studio.

1. Open your SSIS package.
2. Switch to the Control Flow tab.
3. In the SSIS Designer, click on the Event Handlers tab.
4. Select the executable (package, task, or container) for which you want to create an event handler from the dropdown list at the top of the Event Handlers tab.
5. From the Event handler dropdown, choose the specific event you want to handle (e.g., OnError).
6. Click the design surface that appears. This will create a new design surface for your event handler.
7. Drag and drop tasks from the SSIS Toolbox onto this event handler design surface to define the actions that should be performed when the event occurs.

Common Use Cases for Event Handlers

Error Handling (OnError)

This is one of the most common uses. When a task or the package encounters an error, an OnError event handler can be executed. This can involve:

• Logging detailed error information to a file or table.
• Sending an email notification about the error.
• Rolling back transactions.
• Setting package variables to indicate failure.

Pre/Post Execution Logic (OnPreExecute, OnPostExecute)

These events are useful for performing setup or cleanup tasks:

• OnPreExecute: Triggered before a task or container starts executing. You could use this to:
  • Enable an index before a large data load.
  • Truncate a staging table.
  • Set a package variable to the current execution time.
• OnPostExecute: Triggered after a task or container finishes executing (regardless of success or failure). You could use this to:
  • Disable an index after a data load.
  • Archive processed files.
  • Record the completion time of a task.

Information and Progress (OnInformation, OnProgress)

These events can be used for monitoring and detailed logging:

• OnInformation: Used to log informational messages. You can use a Script Task within an event handler to write custom messages to the SSIS log provider.
• OnProgress: Provides granular updates on task execution progress, often used by built-in SSIS logging to show the percentage of rows processed.

Event Handler Scope and Inheritance

Event handlers are associated with specific executables. If an event occurs on a task within a container, SSIS will first look for an event handler configured directly on that task. If none is found, it will then check the parent container, and so on, up to the package level. This inheritance model allows for centralized error handling and logging at the package level.

Example: Logging Errors to a File

Let's create an OnError event handler for a Data Flow Task that logs the error message to a text file.

1. In the SSIS Designer, select the Data Flow Task.
2. Go to the Event Handlers tab.
3. For the selected Data Flow Task, choose the OnError event.
4. Click the design surface to create the handler.
5. Add a File System Task to the event handler design surface.
6. Configure the File System Task to create a file. Set the Operation to CreateFile.
7. In the SourceConnection property (or use a variable), specify the path and filename for your log file (e.g., C:\SSISLogs\ErrorLog_@(string)DTSGlobal::PackageName_@(string)DTSGlobal::TaskName_@(DateTime)GETDATE()_.txt).
8. Add a Script Task to the event handler.
9. In the Script Task editor, set the ReadOnlyVariables property to include the System::ErrorDescription, System::PackageName, and System::TaskName system variables.
10. Write a script (e.g., C#) to append the error details to the file created by the File System Task.

// Example C# Script Task code for OnError event handler
// Assuming variables ErrorDesc, PkgName, TskName are mapped from system variables

using System;
using System.IO;
using Microsoft.SqlServer.Dts.Runtime;

public partial class ScriptMain : IDTSScriptComponent
{
    public void Main()
    {
        string errorDescription = Dts.Variables["System::ErrorDescription"].Value.ToString();
        string packageName = Dts.Variables["System::PackageName"].Value.ToString();
        string taskName = Dts.Variables["System::TaskName"].Value.ToString();
        DateTime executionTime = DateTime.Now;

        string logMessage = string.Format("{0} - Error in package '{1}' task '{2}': {3}\n",
                                          executionTime.ToString("yyyy-MM-dd HH:mm:ss"),
                                          packageName,
                                          taskName,
                                          errorDescription);

        // Assuming the File System Task has already created the file and its path is stored in a variable
        // Let's assume the File System Task's 'DestinationVariable' property is set to a package variable named User::LogFilePath
        string logFilePath = Dts.Variables["User::LogFilePath"].Value.ToString();

        try
        {
            File.AppendAllText(logFilePath, logMessage);
            Dts.TaskResult = (int)ScriptResults.Success;
        }
        catch (Exception ex)
        {
            // If logging fails, we might want to raise another error or log to an alternative destination
            Dts.Events.FireError(0, "Script Task - Logging Error", "Failed to write to log file: " + ex.Message, "", 0);
            Dts.TaskResult = (int)ScriptResults.Failure;
        }
    }
}
            

By implementing event handlers, you gain fine-grained control over your SSIS package's behavior, making your data integration solutions more robust, manageable, and informative.