Update ConfigurationStrings in SSIS package with PowerShell
Here are some functions that allow you to update ConfigurationStrings in a SSIS package (dtsx) using PowerShell:
function UpdateConfigurationStrings($file)
{
$xml = [xml] (Get-Content $file);
$ns = New-Object Xml.XmlNamespaceManager $xml.NameTable;
$ns.AddNamespace("DTS", "www.microsoft.com/SqlServer/Dts");
UpdateConfigurationNodes $xml $ns;
Set-Content $file $xml.OuterXml;
}
function UpdateConfigurationNodes($xml, $ns)
{
$selectConfigurationNodes = "//DTS:Configuration";
$xml.SelectNodes($selectConfigurationNodes, $ns) | foreach { UpdateConfigurationNode $_ $ns; };
}
function UpdateConfigurationNode($configurationNode, $ns)
{
$selectConfigurationString = "./DTS:Property[@DTS:Name='ConfigurationString']";
$configurationStringNode = $configurationNode.SelectSingleNode($selectConfigurationString, $ns);
$oldConfigurationStringValue = $configurationStringNode.'#text';
$newConfigurationStringValue = GetNewConfigurationStringValue $oldConfigurationStringValue;
$configurationStringNode.'#text' = "$newConfigurationStringValue";
}
function GetNewConfigurationStringValue($oldConfigurationStringValue)
{
# implement some logic to determine new value
return "new value";
}