We use Sql Server Integration Services (SSIS) for importing and exporting data. The number of SSIS packages is stil growing and on writing this post we have 35 of them. Changing the database connectionstring for development means opening and editing all packages with Visual Studio until we decided to automate the changes.

In Visual Studio you have a nice visual editor for SSIS packages with the plugin from Microsoft. (https://marketplace.visualstudio.com/items?itemName=SSIS.SqlServerIntegrationServicesProjects) But have you noticed that you can view the code of the package from the solution explorer context menu? Then you see the XML that described the package. XML can be edited with a lot of tools. When we automated we prefer powershell.

We created a powershell script that uses xpath to locate the parts to change. The complete script is at the end of this post. During development of this powershell script we experienced some challenges.

How to read, edit and write XML in powershell

We use Select-Xml to read the complete XML file and locate the node with xpath. Editing can be done on the Node property of the result. To set an attribute you can use the properties of the Node.

To get the xpath to work for SSIS we had to add the DTS namespace. The uri for this can be found in the root node of the SSIS package: DTS = “www.microsoft.com/SqlServer/Dts”

After editing the Save method of the OwnerDocument is called. The parameter should be the full path of the file. The Path property of the Select-Xml result can be used for this. Just make sure you’ve passed the full path to the Select-Xml eg $fileItem.FullName.

Keep formatting to easy view changes

After the script has run you want to view the changes it has made. The default formatting of XML in the save method removes the formatting Microsoft uses when creating and editing in Visual Studio. This makes viewing changes hard.

We’ve found that XmlWriter can be configured to use formatting that resembles the Microsoft way. For this we use the XmlWriterSettings.

Powershell script

We’ve added an extra step to increase the version of the package every time the script has run. Operations will love us for this – oh wait – we are devops … make sure you have the information you need and automate everything 😉

$xpath_version = "/DTS:Executable"
# we use Package Configuration, this will update the connection that is used to load the configuration from a database
$xpath_config = "//DTS:ConnectionManager[@DTS:ObjectName='ConfigurationConnection']/DTS:ObjectData/DTS:ConnectionManager"
$config = [NEW_CONNECTIONSTRING]
$Namespace = @{
    DTS = "www.microsoft.com/SqlServer/Dts"
}

# 1. Find dstx files
$files = Get-ChildItem -Recurse -Path '[PATH_TO_SOURCES]' -Filter '*.dtsx'

foreach($fileItem in $files) {
    $file = $fileItem.FullName

    # 2. edit connection configmanager
    $x = Select-Xml -Path $file -Namespace $Namespace -XPath $xpath_config
    $x.Node.ConnectionString = $config
    $x.Node.OwnerDocument.Save($x.Path)

    # 3. increase version
    $x = Select-Xml -Path $file -Namespace $Namespace -XPath $xpath_version
    $x.Node.VersionBuild = (([int]$x.Node.VersionBuild) + 1).ToString()
    $x.Node.OwnerDocument.Save($x.Path)

    # 4. pretty print
    $xml = [xml](Get-Content -Path $file -Encoding UTF8)
    $StringWriter = New-Object System.IO.StringWriter
    $settings = New-Object System.Xml.XmlWriterSettings
    $settings.Indent = $true
    $settings.NewLineOnAttributes = $true
    $XmlWriter = [System.Xml.XmlWriter]::Create($StringWriter, $settings);
    $xml.WriteContentTo($XmlWriter)
    $XmlWriter.Flush()
    $StringWriter.Flush()
    $StringWriter.ToString() | Out-File -FilePath $file -Encoding utf8
}