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.

SSIS Macros - Part 2 : Get Tasks Without Descriptions

When using SSIS logging or the SSIS Event Handlers to get which tasks have fired and what their results were, I have found that it is very handy to have a good description for each task that is being used in each package.

The few SSIS documenting tools I have seen all take advantage of these descriptions and therefore the benefits of using them greatly outway the effort it takes to define them. However, once your package becomes more complex it can be tedous to have to go to each task click on it, then find the description property in the properties window to see if you remembered to edit the description or not. To ensure that I didn't pull anymore hair out of my head due to this this mundane process, I slapped together a simple macro that will run through a package and see if the Task and Component Descriptions are blank or are still set to the the default.

Here is a result of the macro :

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

    Private Sub GetObjectDescriptions(ByVal frm As frmObjectDescriptions)

        Dim oSSISApp As New SSIS.Application, oPackage As SSIS.Package, oTaskHost As SSIS.TaskHost
        Dim oExec As SSIS.Executable, oEventHandler As SSIS.DtsEventHandler
        Dim oMainPipe As MainPipe

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

            frm.gbPackage.Text = oPackage.Name

            'Loop through executables in package
            For Each oExec In oPackage.Executables
                If oExec.GetType.Name = "TaskHost" Then

                    ' Set the SSIS type
                    oTaskHost = CType(oExec, SSIS.TaskHost)
                    Dim oNode As TreeNode = frm.tvwObjects.Nodes.Add("Task - " & oTaskHost.Name.ToString & " - " & _
                    CheckTaskDesc(oSSISApp, oTaskHost.Description.ToString))

                    ' TODO : Find out how to check if it is a data flow or not
                    Try
                        oMainPipe = CType(oTaskHost.InnerObject, MainPipe)

                        ' Loop through components
                        For i As Integer = 0 To oMainPipe.ComponentMetaDataCollection.Count - 1
                            oNode.Nodes.Add("Dataflow - " & oMainPipe.ComponentMetaDataCollection.Item(i).Name.ToString & " - " & _
                            CheckPipeCompDesc(oSSISApp, oMainPipe.ComponentMetaDataCollection.Item(i).Description.ToString))
                        Next
                    Catch ex As Exception

                    End Try
                End If
            Next

        Catch ex As Exception
            MessageBox.Show(ex.Message.ToString)
        Finally

        End Try

    End Sub

If you are like me and have seen the benefit of having these descriptions defined and want to avoid the headache this process can cause I would recommend take a bit of time to set this up. Your receding hairline will thank you as mine did.

Ashes - 3rd Test (Perth) : Day 1 Prediction

Well I guess that answers the Monty question. A very confincing argument for a tight test match. I, however, believe it doesn't matter, unless England score 550 in the first innings the Aussies will still take this one with a fiver from Warnie in the 2nd innings!

SSIS Macros - Part 1 : Reset Task IDs

As a first blog and seeing that I have found myself doing alot of just SSIS development on my current project, I thought I would share a few nice-to-have visual studio 2005 macros I have been using that have made my SSIS development alot easier.

The macro for this post involves resetting Task/Container Guids in SSIS packages. For those who don't know, each Task or Container in an SSIS package will have an ID propery. This ID property is a unique GUID which uniquely identifies the object. So if you are using SSIS logging or some kind of logging through the event handlers you find that using these GUIDs can be very helpful in logging and auditing the tasks in all your packages.

However, one of the problems that may arise by using these IDs is that if you copy and paste a task into a different container (Loop or Sequence) or package the Task ID will remain the same as the source one that you copied. Therefore it will not uniquely identity that task in the package (if it exists in different containers) or in the packages of the SSIS project. This can be a real headache when you are trying to find which task caused an error or executed for a certain period of time.

If you are copying and pasting packages the same problem will occur. However, it is easy to overcome by using "Generate New ID" through the ID property of the package. This is not as easy when dealing with tasks.

The macro I have included in this post will reset all the task GUIDs in a package.
All it does is use the SSIS object model to loop through all the tasks inside a given package, find the tasks ID and then do a find and replace in the packages XML.

Note: Be aware that the macro will save the package after the IDs have been reset, so do not run it if you do not want other changes you have made to the package to be saved.

I have included sample code that will help you get started writing your own macros. This code will only reset IDs for tasks, so you will need to add your own code for containers.

Note: The object model sees each container (sequence and loop) or package as having a collection of tasks. Therefore if you want to reset all the guids you will have to write code to loop through the package, the containers and all nested containers.

Source Code:

Sub ResetGuids()

        Dim oApp As New SSIS.Application
        Dim oExec As SSIS.Executable, taskHost As SSIS.TaskHost
        Dim aszTaskID(0) As String, gdNewGuid As Guid

        Try
            ' Get package path
            Dim oPackage As SSIS.Package = oApp.LoadPackage(DTE.ActiveDocument.FullName, Nothing)

            ' Loop through tasks
            For Each oExec In oPackage.Executables
                taskHost = CType(oExec, SSIS.TaskHost)

                ' Store task Guids in an array
                ' Set value and grow array size
                aszTaskID(aszTaskID.GetUpperBound(0)) = taskHost.ID
                ReDim Preserve aszTaskID(aszTaskID.GetUpperBound(0) + 1)
            Next

            ' Edit package XML 
            DTE.ExecuteCommand("View.ViewCode")

            ' Loop through task GUIDS in the array
            For i As Integer = 0 To aszTaskID.GetUpperBound(0) - 1

                DTE.Find.FindWhat = aszTaskID(i).ToString
                DTE.Find.ReplaceWith = "{" & System.Guid.NewGuid.ToString.ToUpper & "}"

                ' Set find criteria
                DTE.Find.Target = vsFindTarget.vsFindTargetCurrentDocument
                DTE.Find.MatchCase = False
                DTE.Find.MatchWholeWord = True
                DTE.Find.MatchInHiddenText = False
                DTE.Find.Action = vsFindAction.vsFindActionReplaceAll

                If (DTE.Find.Execute() = vsFindResult.vsFindResultNotFound) Then
                    MsgBox("No values were found")
                End If
            Next

            DTE.ActiveWindow.Close(vsSaveChanges.vsSaveChangesNo)
            DTE.ActiveDocument.Save()
        Catch ex As Exception
            MsgBox(ex.Message.ToString)
        End Try

    End Sub

 

-- Updated on the 16 Feb 2007

The previous sample only looped through tasks in the package i.e. not tasks in sequence or loop containers. Here is a sample that will capture all IDs for all tasks and containers. This sample will recursively loop through all the package objects.

Public Sub ResetsEachExecutablesGuid()

        Dim oApp As New SSIS.Application, aszTaskID(0) As String
        Dim oExec As SSIS.Executable, taskHost As SSIS.TaskHost
        Dim gdNewGuid As Guid, SSISObject As System.Object

        Try
            ' Get package path
            Dim oPackage As SSIS.Package = oApp.LoadPackage(DTE.ActiveDocument.FullName, Nothing)

            ' Loop through tasks
            For Each oExec In oPackage.Executables
                SSISObject = oExec
                If oExec.GetType.Name.ToLower <> "taskhost" Then
                    Call LoopThroughContainer(SSISObject, aszTaskID)
                End If

                ' Capture Task IDs
                aszTaskID(aszTaskID.GetUpperBound(0)) = SSISObject.ID
                ReDim Preserve aszTaskID(aszTaskID.GetUpperBound(0) + 1)
            Next

            ' Edit package XML 
            DTE.ExecuteCommand("View.ViewCode")

            ' Loop through task ids captured
            For i As Integer = 0 To aszTaskID.GetUpperBound(0) - 1

                DTE.Find.FindWhat = aszTaskID(i).ToString
                DTE.Find.ReplaceWith = "{" & System.Guid.NewGuid.ToString.ToUpper & "}"

                ' Set find criteria
                DTE.Find.Target = vsFindTarget.vsFindTargetCurrentDocument
                DTE.Find.MatchCase = False
                DTE.Find.MatchWholeWord = True
                DTE.Find.MatchInHiddenText = False
                DTE.Find.Action = vsFindAction.vsFindActionReplaceAll

                If (DTE.Find.Execute() = vsFindResult.vsFindResultNotFound) Then
                    MsgBox("No values were found")
                End If
            Next

            DTE.ActiveWindow.Close(vsSaveChanges.vsSaveChangesNo)
            DTE.ActiveDocument.Save()
        Catch ex As Exception
            MsgBox(ex.Message.ToString)
        End Try

    End Sub

    Private Function LoopThroughContainer(ByVal oContainer As System.Object, ByVal aszTaskID() As String)

        Dim oExec As SSIS.Executable, SSISObject As System.Object

        For Each oExec In oContainer.Executables
            SSISObject = oExec
            If SSISObject.GetType.Name.ToLower <> "taskhost" Then
                Call LoopThroughContainer(SSISObject, aszTaskID)
            End If

            ' Capture Task IDs
            aszTaskID(aszTaskID.GetUpperBound(0)) = SSISObject.ID
            ReDim Preserve aszTaskID(aszTaskID.GetUpperBound(0) + 1)
        Next

    End Function