Cómo documentar tu ETL del SSIS con PowerShell

Hace unas semanas me enfrentaba a tener que revisar un ETL hecho con el SQL Server Integration Services (SSIS) que no sólo yo lo estaba desarrollando. El objetivo que tenía era poder preparar los orígenes de datos de dicho ETL. Necesitaba conocer qué bases de datos, tablas, campos, etc. necesitaba para , por ejemplo, poder establecer permisos o comprobar que se recogían todos los datos de donde se tenían que recoger.

Todos los que conozcáis el SSIS sabéis que un proyecto importante puede tener cientos de cajitas con las transformaciones (si no son miles), y no se vosotros, pero no quería perder el tiempo pinchando en cada una de ellas. Teniendo en cuenta que probablemente sea una tarea que tenga que hacer habitualmente, decidí pensar e indagar si habría otra forma. Y por suerte, la encontré.

Los archivos «.dtsx» que conforman un proyecto de SSIS son archivos XML que contienen toda la información que conforma ese paquete, por lo que sólo tenía que descubrir, cómo navegar por ese xml.

Cosas importantes a tener en cuenta:

• Como mi objetivo es mirar la parte de BBDD, se fija sólo en los SQLCOMMANDs.
• El SSIS tiene muchos tipos de objetos, pero este código se fija sólo en: OLEDB Source, Lookups y OLEDB Command.
• Crea un documento WORD, con encabezados para que esté organizado y sea más sencillo de leer.
• Usa PowerShell
• El directorio destino, debe existir.

Una vez definas el origen y el destino, sólo tendrás que lanzar este script en el ISE de PowerShell.

#Preparing the info about .dtsx
$path = "C:\Projects\BI\SSIS Test"
$extension = ".dtsx"

#Preparing word generation
$docfile = "C:\test\DocumentacionDTSX.docx"


# Create a new instance/object of MS Word
$MSWord = New-Object -ComObject Word.Application

# Make MS Word visible
$MSWord.Visible = $True

# Add a new document
$mydoc = $MSWord.Documents.Add()


$Section = $mydoc.Sections.Item(1)
$Header = $Section.Headers.Item(1)
$Footer = $Section.Footers.Item(1)
$Footer.PageNumbers.Add()

# Create a reference to the current document so we can begin adding text
$myText = $MSWord.Selection
$myText.Style = [Microsoft.Office.Interop.Word.WdBuiltinStyle]::wdStyleHeading1
$myText.TypeText("Documentation Generation Info")
$myText.TypeParagraph()

$myText = $MSWord.Selection
$date = Get-date -Format "dddd dd/MM/yyyy HH:mm K"
$myText.Font.Bold = 1
$myText.TypeText("Generation date: ")
$myText.Font.Bold = 0
$myText.TypeText($date)
$myText.TypeParagraph()

#I'm going to loop through all the dtsx to generate the documentation
Get-ChildItem $path |
Foreach-Object {
   If ($_.Extension -eq $extension)
   {
         # read .dtsx into XML variable
        [xml] $myxml = Get-Content $_.FullName

        # Create Title 1
        $myText = $MSWord.Selection
        $myText.Style = [Microsoft.Office.Interop.Word.WdBuiltinStyle]::wdStyleHeading1
        $myText.TypeText($_.Name)
        $myText.TypeParagraph()

        # Objeto OLEDB Source
        $HasAlreadyPutHeading2 = $false
        
        $myxml | Select-XML -XPath "//component[@componentClassID='Microsoft.OLEDBSource']" | ForEach-Object {
            # Creates if not exists, Title 2
            if ($HasAlreadyPutHeading2 -eq $false)
            {
                $myText = $MSWord.Selection
                $myText.Style = [Microsoft.Office.Interop.Word.WdBuiltinStyle]::wdStyleHeading2
                $myText.TypeText("Microsoft.OLEDBSource")
                $myText.TypeParagraph()     
                $HasAlreadyPutHeading2 = $true    
            }

            # Creates if not exists, Title 2
            $myText = $MSWord.Selection
            $myText.Style = [Microsoft.Office.Interop.Word.WdBuiltinStyle]::wdStyleHeading3
            $myText.TypeText($_.Node.Name)
            $myText.TypeParagraph()            

            #I pick up the children
            [xml] $childs = $_.Node.OuterXml 

            
            $myText = $MSWord.Selection            

            $childs| Select-XML -XPath "//connection[@connectionManagerRefId]"  | ForEach-Object {
                # Register the name of the connection
                $myText.Style = [Microsoft.Office.Interop.Word.WdBuiltinStyle]::wdStyleListBullet
                $myText.Font.Bold = 1
                $myText.TypeText('Conexión: ')
                $myText.Font.Bold = 0
                $myText.TypeText($_.Node.connectionManagerRefId)
                $myText.TypeParagraph()                     
            }

            $childs| Select-XML -XPath "//property[@name='SqlCommand']"  | ForEach-Object {
                # Register SQL Command
                $myText.Style = [Microsoft.Office.Interop.Word.WdBuiltinStyle]::wdStyleListBullet
                $myText.Font.Bold = 1
                $myText.TypeText('Comando SQL: ')
                $myText.TypeParagraph()             
                $myText.Font.Bold = 0
                $myText.Style = [Microsoft.Office.Interop.Word.WdBuiltinStyle]::wdStylePlainText
                $myText.TypeText($_.Node.InnerText)
                $myText.TypeParagraph()                                 
            }

        }

        # Objeto Lookup
        $HasAlreadyPutHeading2 = $false

        $myxml | Select-XML -XPath "//component[@componentClassID='Microsoft.Lookup']" | ForEach-Object {
            # Creates if not exists, Title 2
            if ($HasAlreadyPutHeading2 -eq $false)
            {
                $myText = $MSWord.Selection
                $myText.Style = [Microsoft.Office.Interop.Word.WdBuiltinStyle]::wdStyleHeading2
                $myText.TypeText("Microsoft.Lookup")
                $myText.TypeParagraph()     
                $HasAlreadyPutHeading2 = $true    
            }

            # Creates if not exists, Title 3
            $myText = $MSWord.Selection
            $myText.Style = [Microsoft.Office.Interop.Word.WdBuiltinStyle]::wdStyleHeading3
            $myText.TypeText($_.Node.Name)
            $myText.TypeParagraph()            

            #I pick up the children
            [xml] $childs = $_.Node.OuterXml 
            
            $childs| Select-XML -XPath "//connection[@connectionManagerRefId]"  | ForEach-Object {
                # Register the name of the connection
                $myText.Style = [Microsoft.Office.Interop.Word.WdBuiltinStyle]::wdStyleListBullet
                $myText.Font.Bold = 1
                $myText.TypeText('Conexión: ')
                $myText.Font.Bold = 0
                $myText.TypeText($_.Node.connectionManagerRefId)
                $myText.TypeParagraph()                     
            }

            $childs| Select-XML -XPath "//property[@name='SqlCommand']"  | ForEach-Object {
                # Register SQL Command
                $myText.Style = [Microsoft.Office.Interop.Word.WdBuiltinStyle]::wdStyleListBullet
                $myText.Font.Bold = 1
                $myText.TypeText('Comando SQL: ')
                $myText.TypeParagraph()             
                $myText.Font.Bold = 0
                $myText.Style = [Microsoft.Office.Interop.Word.WdBuiltinStyle]::wdStylePlainText
                $myText.TypeText($_.Node.InnerText)
                $myText.TypeParagraph()                                 
            }
            
        }

        # Objeto Comando OLEDB
        $HasAlreadyPutHeading2 = $false

        $myxml | Select-XML -XPath "//component[@componentClassID='Microsoft.OLEDBCommand']" | ForEach-Object {
            # Creates if not exists, Title 2
            if ($HasAlreadyPutHeading2 -eq $false)
            {
                $myText = $MSWord.Selection
                $myText.Style = [Microsoft.Office.Interop.Word.WdBuiltinStyle]::wdStyleHeading2
                $myText.TypeText("Microsoft.OLEDBCommand")
                $myText.TypeParagraph()     
                $HasAlreadyPutHeading2 = $true    
            }

            # Creates if not exists, Title 3
            $myText = $MSWord.Selection
            $myText.Style = [Microsoft.Office.Interop.Word.WdBuiltinStyle]::wdStyleHeading3
            $myText.TypeText($_.Node.Name)
            $myText.TypeParagraph()            

            #I pick up the children
            [xml] $childs = $_.Node.OuterXml 
            
            $childs| Select-XML -XPath "//connection[@connectionManagerRefId]"  | ForEach-Object {
                # Register the name of the connection
                $myText.Style = [Microsoft.Office.Interop.Word.WdBuiltinStyle]::wdStyleListBullet
                $myText.Font.Bold = 1
                $myText.TypeText('Conexión: ')
                $myText.Font.Bold = 0
                $myText.TypeText($_.Node.connectionManagerRefId)
                $myText.TypeParagraph()                     
            }

            $childs| Select-XML -XPath "//property[@name='SqlCommand']"  | ForEach-Object {
                # Register SQL Command
                $myText.Style = [Microsoft.Office.Interop.Word.WdBuiltinStyle]::wdStyleListBullet
                $myText.Font.Bold = 1
                $myText.TypeText('Comando SQL: ')
                $myText.TypeParagraph()             
                $myText.Font.Bold = 0
                $myText.Style = [Microsoft.Office.Interop.Word.WdBuiltinStyle]::wdStylePlainText
                $myText.TypeText($_.Node.InnerText)
                $myText.TypeParagraph()                                 
            }
            
        }
   }
}


$saveFormat = [Microsoft.Office.Interop.Word.WdSaveFormat]::wdFormatDocumentDefault
$mydoc.SaveAs([ref][system.object]$docfile, [ref]$saveFormat)
$mydoc.Close()
$MSWord.Quit()

# Clean up Com object
$null =
[System.Runtime.InteropServices.Marshal]::ReleaseComObject([System.__ComObject]$MSWord)
Remove-Variable MSWord

¡Espero que os sea tan útil como a mi!