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