Using Powershell to read and modify SSIS packages

SSIS / Powershell / Integration Services / EzApi / BIML

Introduction

In these days developing in SSIS, you should be using a generation framework (BIML or EzApi. The benefits era a bit obvious:

  1. Your packages will have consistent layout and logic
  2. It’s easy and fast to apply the same change to all packages
  3. No more mouse programming (almost :))
  4. It’s the right way to do metadata driven ETL

The problem

In a client I had a project with 97 packages. Each one of them started as copy of the first one. The request was to change the value of a single variable on all of them.

Without a framework to help me, how to to change all the packages? The only answer that came to me was to use Powershell. You can do something quick and dirty like this:


#Loop the dtsx files
Get-ChildItem *.dtsx | ForEach-Object { 

    #obtain package as XML
    $dtsXML = [xml](Get-Content $_.FullName)

    #add xml namespace magic
    $ns = [System.Xml.XmlNamespaceManager]($dtsXML.NameTable)
    $ns.AddNamespace("DTS", "www.microsoft.com/SqlServer/Dts")

    #now we can use XPath to get the parameter
    $myParameter = $dtsXML.SelectSingleNode("/DTS:Executable/DTS:Variables/DTS:Variable[@DTS:ObjectName='AuditId'][1]", $ns)

    #only if it exists
    if($myParameter)
    {
        #Set new value
        $myParameter.VariableValue.'#text' = "0"
    }

    #XmlWriterSetting to save the package indented
    [System.Xml.XmlWriterSettings] $settings = New-Object -TypeName System.Xml.XmlWriterSettings
    #Indent the XML
    $settings.Indent = $true 
    $settings.NewLineOnAttributes = $true 

    #XmlWriter with the options to indent
    [System.Xml.XmlWriter] $writer = [System.Xml.XmlWriter]::Create($_.FullName,$settings)
    $dtsXML.Save($writer)

    #Cleanup
    $writer.Close()
}

You can even add expressions to your variables:


$attr = $dts.CreateAttribute("DTS", "Expression", "www.microsoft.com/SqlServer/Dts")
$attr.Value = "@[`$Project::MaxExtractionRecords]"
$param.Attributes.Append($attr)

Conclusion

Once again Powershell came to my rescue and saved me a few hours of work.

Have fun!

 Share!