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.