Catch Custom Events with SSIS Event Handler

Background

When I started working for PensionDanmark summer 2011, we started looking into using SSIS as the main ETL tool for the Datawarehouse team. One of the main requirements were to be able to bring SSIS into the enterprise scale, and to be able to design and implement a pattern that will be able to cover a wide range of ETL related tasks though out the enterprise.

I knew that SSIS is lacking some of these enterprise needs, and when I saw Andy Leonard approach using a framework, I decided to try and implement one similar in PensionDanmark. What really got my attention was the ability to have a meta data driven approach to the execution of SSIS, and the ability to centralize the settings of logging, and by that to allow multiple child package to be simple and yet to inherit a complex set of events logging, that can be maintained easily, without effecting the child packages.

After the implementation of the first version of the framework, we had a request from the users to be able to report on non standard events. I thought that it should be quite simple, as SSIS has a custom event, and SSIS Script Task Component has the ability to fire custom events. But I was wrong to think it was that simple, because the SSIS event handler has no implementation for the custom event.

The problem description is like this: the frameworks central logging feature is build on the fact that the event handlers of the parent package (the framework package) is able to catch events that are fired in the child package too. But there is no event handler for the custom event, so what can we do?

A Solution

What we need is a workaround, that will allow us to catch a custom event, fired in the child package, using an event handler of the parent package. I started to look into the types of events which have an event handler, and to see weather I could hijack one, and make it act as a custom event handler, and than I saw it, the perfect victim for the task,  the VariableChanged event handler!

Why is it perfect?

• I’ve never used it in production in my years developing SSIS packages.
• The event is fully controlled by the developer. That is the SSIS developer chooses to enable this event on a variable. This is something we cannot do with any other event!
• I believe that it’s by far a event that one is willing to live without for the price of having custom event in a centralize manner. I know I would :-).

Let’s take a look on how we actually doing it:

Here is the parent package, it’s made simple, in order to better illustrate the event catch scenario. An SSIS Framework looks of course more complex than this simple parent package.

The parent package has a script component in the OnVariableValueChanged. With that script we will need to catch the variables which are available for this type of event, and use them to log the real event that is fired by the child package.

This is how the script looks like:

'The follwing variables are accesable from this type of event handler:
'System::TaskName
'System::SourceName
'System::VariableDescription
'System::VariableID
'System::VariableName
'System::VariableValue

Public Sub Main()
    Dim S As String

    'Building a string that proof that the values of the variables
    ' is originated from the event fires in the child package
    S = "VariableName: " & Dts.Variables("System::VariableName") _
      .Value.ToString() & vbCrLf _
      & "VariableID: " & Dts.Variables("System::VariableID") _
      .Value.ToString() & vbCrLf _
      & "VariableDescription: " & Dts.Variables("System::VariableDescription") _
      .Value.ToString() & vbCrLf _
      & "VariableValue: " & Dts.Variables("System::VariableValue") _
      .Value.ToString() & vbCrLf _
      & "TaskName: " & Dts.Variables("System::TaskName") _
      .Value.ToString() & vbCrLf _
      & "SourceName: " & Dts.Variables("System::SourceName") _
      .Value.ToString()

    'Showing the string value as a message box
    MsgBox(S)

    Dts.TaskResult = ScriptResults.Success
End Sub

Note I’m not telling you here how to log the event, but just showing which variables are available for usage, and how to manipulate this variables from the event fire method

Here is a screen shot of the child package:

The first script task is there just to proof the fact that the event is not fired, unless we set the event on a variable.

The second script task is where the magic is done, let’s look into the code:

Public Sub Main()

        Dts.Events().FireCustomEvent("OnVariableValueChanged", _
                                     "", _
                                     New Object(3) _
                                        { _
                                        "First Value", _
                                        "Second Value", _
                                        "Third Value", _
                                        "Forth Value"}, _
                                     "", _
                                     False)

        Dts.TaskResult = ScriptResults.Success
    End Sub

What we are doing here is this, we fire a custom event, of type OnVariableValueChanged. The FireCustomEvent can take an object, as the third parameter, and this object is later used by the SSIS Event Handler. I’ve doing some testing, and found out that at least 4 items can be included in this object, and that this 4 objects correspond to 4 SSIS system variables, which are accessible in the SSIS Event Handler. All 4 are strings, so we must use strings, to prevent errors.

To illustrate that, I’m sending 4 text values, let’s look into what happens when we ran the parent package:

Let’s zoom in:

In real life, we will use this 4 strings to send information about the custom event, it’s source, level, severity what ever is relevant for the specific system requirements.

The entire project is available for download.