Macro for BIDS to list event handlers

Imports System
Imports EnvDTE
Imports EnvDTE80
Imports System.Diagnostics
Imports System.Windows.Forms
Imports SSIS = Microsoft.SqlServer.Dts.Runtime

Public Class frmEventHandlers
    Inherits Form

    Public lvwEventHandler As ListView

    Public Sub New()
    lvwEventHandler = New ListView
    lvwEventHandler.View = View.Details
    lvwEventHandler.GridLines = True

    lvwEventHandler.Columns.Add("object")
    lvwEventHandler.Columns.Add("event")

    Me.Controls.Add(lvwEventHandler)
    lvwEventHandler.Dock = DockStyle.Fill

    Dim btn As New Button
    btn.Text = "OK"
    btn.DialogResult = DialogResult.OK

    Me.Controls.Add(btn)
    btn.Dock = DockStyle.Bottom
    End Sub
End Class

Public Module my_ssis

    Public Sub List_EventHandlers()

    Dim oSSISApp As New SSIS.Application
    Dim oPackage As SSIS.Package
    Dim frm As New frmEventHandlers

    ' Get package object
    MsgBox(DTE.ActiveDocument.FullName)
    oPackage = oSSISApp.LoadPackage(DTE.ActiveDocument.FullName, Nothing)

    frm.Text = oPackage.Name
    _List_EventHandlers(frm, oPackage.Name, oPackage.EventHandlers, oPackage.Executables)
    frm.ShowDialog()

    End Sub

    Private Sub _List_EventHandlers(ByVal frm As frmEventHandlers, ByVal name As String, ByVal h As SSIS.DtsEventHandlers, ByVal e As SSIS.Executables)
    Dim handler As SSIS.DtsEventHandler
    Dim exec As SSIS.Executable

    If Not h Is Nothing Then
    For Each handler In h
    frm.lvwEventHandler.Items.Add(New ListViewItem(New String() {name, handler.Name}))

    _List_EventHandlers(frm, handler.Name, Nothing, handler.Executables)
    Next
    End If

    If Not e Is Nothing Then
    For Each exec In e
    Select Case exec.GetType.Name
    Case "ForEachLoop"
    _List_EventHandlers(frm, CType(exec, SSIS.ForEachLoop).Name, CType(exec, SSIS.ForEachLoop).EventHandlers, CType(exec, SSIS.ForEachLoop).Executables)
    Case "ForLoop"
    _List_EventHandlers(frm, CType(exec, SSIS.ForLoop).Name, CType(exec, SSIS.ForLoop).EventHandlers, CType(exec, SSIS.ForLoop).Executables)
    Case "Sequence"
    _List_EventHandlers(frm, CType(exec, SSIS.Sequence).Name, CType(exec, SSIS.Sequence).EventHandlers, CType(exec, SSIS.Sequence).Executables)
    Case "TaskHost"
    _List_EventHandlers(frm, CType(exec, SSIS.TaskHost).Name, CType(exec, SSIS.TaskHost).EventHandlers, Nothing)
    Case Else
    frm.lvwEventHandler.Items.Add(New ListViewItem(New String() {name, exec.GetType.Name}))
    End Select
    Next
    End If
    End Sub
End Module
Advertisements