Tim Van Wassenhove home

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";
}