Home > Uncategorized > Catch Custom Events with SSIS Event Handler

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

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.

The Parent Package Event Handler

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 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:

Running the parent package

Let’s zoom in:

The message box

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.

About these ads
Categories: Uncategorized Tags: ,
  1. Darryll Petrancuri
    February 7, 2012 at 6:35 pm

    I’m not sure I’m following completely. Is the second script in the Child package? Does it raise an event in the Parent Package? This design pattern seems to suggest you have to manually add the second script task to the Child package? What if you want to ‘automatically’ add the custom event ‘raiser’ to the child package?

    • February 7, 2012 at 9:13 pm

      Hi Darryll,

      Thank you for your time!

      You are right, the child package needs to write some custom code for each custom event that is to be raised. I cannot see any other way around it, as we are talking on custom events, which by definition is scoped to the child package logic.

      The advantage in this pattern is not that you can push custom event handlers logic to your child packages, but rather to let you centralize what to do, when these events occur. It gives your the ability to use the same custom logging code on both official events and custom events.

      • Darryll Petrancuri
        February 7, 2012 at 9:15 pm

        That’s what I thought. So, redirecting a little bit, can we dynamically, at runtime, (from an event that is raised that has a listener in a parent package) change the RaiseChangeEvent property of a variable in a child package from False to true?

        Thanks for any insights!

  2. February 7, 2012 at 9:24 pm

    Hi Darryll,

    I do not know of a way which will allow the parent to change a property in the child package in runtime of the child package. The communication can only go from child to parent, as far as I know.

    It’s should be possible for the parent to create a config file in parent runtime, before the execution of the child package begins, and by that to manipulate any aspect of the child package, like RasieChangeEvent for a specific variable, but that will be rather cumbersome.

    Why is this desired? What are you trying to achieve?

    /Rafi

    • Darryll Petrancuri
      February 7, 2012 at 9:32 pm

      I want to be able to dynamically turn on / off logging (and firing) of OnVariableValueChanged events for Child variables from an executing parent Package when it loads (or immediately before if necessary via SSISXML manipulation)…

      • February 7, 2012 at 9:38 pm

        OK, one option is quite simple, you can choose to ignore the event in the package event handler of the parent, when you wish not to react on this event. Another option is to use a config file, which the parent will manipulate in runtime prior to the execution of the child package.

        /Rafi

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: