SSIS Macros - Part 3 : Get Task Event Handlers

Anyone who has been using SSIS for a reasonable amount of time will probably be very familiar with SSIS event handlers. I have found this to be a very useful feature and it always one to avoid the spider web nightmare that can occur when you are trying to capture each task's error and end up having a failure constraint coming from each of them.

However when using events one has to be careful at which level you handle them. If you use the OnError event at the package level and the task level, and the task fails, you will notice that the event will fire twice due to the fact that events bubble up from each task to their container. This can easily be overcome by setting the task system variable propogate in the event handler to false. However whenever you are developing a complex task or have need to edit one, it is difficult without a lot of clicking to find out which tasks have event handler, which have progate set to false and which are being handled at the container or package level.

Once again, I thought this would be an ideal time to write a quick macro that would allow me to see the event handler information for more complex packages.

Here is what is displayed by the macro I use:

Below is a sample of the code I used to get this information:

        Dim oSSISApp As New SSIS.Application, oPackage As SSIS.Package, oTaskHost As SSIS.TaskHost
        Dim oExec As SSIS.Executable, oEventHandler As SSIS.DtsEventHandler, oContainer As Object, i As Integer, j As Integer

        Try
            ' Get package object
            oPackage = oSSISApp.LoadPackage(DTE.ActiveDocument.FullName, Nothing)
            frm.gbPackage.Text = oPackage.Name.ToString

            ' Loop through events in the package
            For Each oEventHandler In oPackage.EventHandlers
                frm.lvwEventHandler.Items.Add(frm.lvwEventHandler.Items.Count + 1)

                i = frm.lvwEventHandler.Items.Count - 1
                frm.lvwEventHandler.Items(i).SubItems.Add(oPackage.GetType.Name.ToString)
                frm.lvwEventHandler.Items(i).SubItems.Add(oPackage.Name.ToString)
                frm.lvwEventHandler.Items(i).SubItems.Add(oPackage.EventHandlers.Count.ToString)
                frm.lvwEventHandler.Items(i).SubItems.Add(oEventHandler.Name.ToString)
                frm.lvwEventHandler.Items(i).SubItems.Add(oEventHandler.Variables.Item("Propagate").Value.ToString)
            Next

            ' Loop through executables in package
            For Each oExec In oPackage.Executables
                ' Check that the executable is a task
                If oExec.GetType.Name = "TaskHost" Then

                    ' Set the SSIS type
                    oTaskHost = CType(oExec, SSIS.TaskHost)

                    ' If no events
                    If oTaskHost.EventHandlers.Count = 0 Then
                        frm.lvwEventHandler.Items.Add(frm.lvwEventHandler.Items.Count + 1)

                        i = frm.lvwEventHandler.Items.Count - 1
                        frm.lvwEventHandler.Items(i).SubItems.Add(oExec.GetType.Name.ToString)
                        frm.lvwEventHandler.Items(i).SubItems.Add(oTaskHost.Name.ToString)
                        frm.lvwEventHandler.Items(i).SubItems.Add(oTaskHost.EventHandlers.Count.ToString)
                    End If

                    ' Loop through event handlers
                    For Each oEventHandler In oTaskHost.EventHandlers
                        frm.lvwEventHandler.Items.Add(frm.lvwEventHandler.Items.Count + 1)

                        j = frm.lvwEventHandler.Items.Count - 1
                        frm.lvwEventHandler.Items(j).SubItems.Add(oExec.GetType.Name.ToString)
                        frm.lvwEventHandler.Items(j).SubItems.Add(oTaskHost.Name.ToString)
                        frm.lvwEventHandler.Items(j).SubItems.Add(oTaskHost.EventHandlers.Count.ToString)
                        frm.lvwEventHandler.Items(j).SubItems.Add(oEventHandler.Name.ToString)
                        frm.lvwEventHandler.Items(j).SubItems.Add(oEventHandler.Variables.Item("Propagate").Value.ToString)
                    Next
                End If
            Next
        Catch ex As Exception

        End Try

    End Sub

I hope some will find benefit from this, I definitely have.