Powershell - Modify Server Name in SQL 2005 SSIS Connections

Param(
 $sourceSQLServerName = "[ENTER NAME]",      # Production SQL Server to get a copy SSIS packages
 $destinationSQLServerName = "[ENTER NAME]", # Test SQL Server to load modified SSIS packages
 $MSDBFolderName = "[ENTER NAME]",           # Folder name within MSDB where SSIS packages are stored
 $oldSQLServerName = "[ENTER NAME]",         # Old SQL Server name that will be replaced by new name in SSIS package connections
 $newSQLServerName = "(local)",              # New SQL Server name that will replace old name in SSIS package connections
 $oldDB2ServerName = "[ENTER NAME]",         # Old DB2 Server name that will be replaced by new name in SSIS package connections
 $newDB2ServerName = "[ENTER NAME]",         # New DB2 Server name that will replace old name in SSIS package connections
 $oldFileConnectionPath = "[ENTER PATH]",    # Old file path that will be replaced by new file path in SSIS package connections
 $newFileConnectionPath = "[ENTER PATH]"     # New file path that will replace old file path in SSIS package connections
 )
 
 cls
 
 add-pssnapin sqlservercmdletsnapin100 -ErrorAction SilentlyContinue
 
 $SSISFolderPath = ""      # Folder path on local file system where copy of SSIS packages will be stored prior to modifications
 $newSSISFolderPath = ""   # Folder path on local file system where modified SSIS packages will be stored
 $xml = ""
 $ns = ""
 
 
 # ***************************************************** DECLARE FUNCTIONS ***************************************************************
 
 function CopySSISPackagesFromMSDBToFileSystem
 {
 $sqlCmd = ""
 
 if ($MSDBFolderName)
 {
 $sqlCmd = "WITH cte AS (
 SELECT cast(foldername as varchar(max)) as folderpath, folderid
 FROM msdb..sysdtspackagefolders90
 WHERE parentfolderid = '00000000-0000-0000-0000-000000000000'
 UNION ALL
 SELECT cast(c.folderpath + '\' + f.foldername as varchar(max)), f.folderid
 FROM msdb..sysdtspackagefolders90 f
 INNER JOIN cte c ON c.folderid = f.parentfolderid
 )
 SELECT c.folderpath,p.name,CAST(CAST(packagedata AS VARBINARY(MAX)) AS VARCHAR(MAX)) as pkg
 FROM msdb..sysdtspackages90 p LEFT JOIN cte c ON c.folderid = p.folderid
 WHERE c.folderpath = '$MSDBFolderName' "
 }
 else
 {
 $sqlCmd = "WITH cte AS (
 SELECT cast(foldername as varchar(max)) as folderpath, folderid
 FROM msdb..sysdtspackagefolders90
 WHERE parentfolderid = '00000000-0000-0000-0000-000000000000'
 UNION ALL
 SELECT cast(c.folderpath + '\' + f.foldername as varchar(max)), f.folderid
 FROM msdb..sysdtspackagefolders90 f
 INNER JOIN cte c ON c.folderid = f.parentfolderid
 )
 SELECT c.folderpath,p.name,CAST(CAST(packagedata AS VARBINARY(MAX)) AS VARCHAR(MAX)) as pkg
 FROM msdb..sysdtspackages90 p LEFT JOIN cte c ON c.folderid = p.folderid
 WHERE c.folderpath IS NULL "
 }
 
 $packages = Invoke-Sqlcmd -MaxCharLength 10000000 -ServerInstance $sourceSQLServerName -Query $sqlCmd
 
 foreach ($pkg in $Packages)
 {
 $pkgName = $Pkg.name
 $folderPath = $Pkg.folderpath
 $fullfolderPath = "c:\temp\$sourceSQLServerName\$folderPath"
 if(!(test-path -path $fullfolderPath))
 {
 mkdir $fullfolderPath | Out-Null
 }
 $pkg.pkg | Out-File -Force -encoding ascii -FilePath "$fullfolderPath\$pkgName.dtsx"
 }
 }
 
 function UpdateConnections ($connectionNodes) 
 { 
 foreach ($connectionNode in $connectionNodes)
 {
 $connectionManager = $connectionNode.SelectSingleNode("DTS:ObjectData/*", $ns)
 
 if ($connectionManager.ConnectionString)
 {
 $oldConnectionString = $connectionManager.ConnectionString;
 
 # If the connection string contains the old SQL Server name, replace with new SQL Server name
 if ($oldConnectionString.Contains($oldSQLServerName))
 {
 $newConnectionString = $oldConnectionString.Replace($oldSQLServerName, $newSQLServerName)
 $connectionManager.ConnectionString = "$newConnectionString"
 }
 }
 else
 {
 foreach ($property in $connectionManager.Property)
 {
 if ($property.Name -eq "ConnectionString")
 {
 $oldConnectionString = $property.'#text';
 
 # If the connection string contains the old SQL Server name, replace with new SQL Server name
 if ($oldConnectionString.Contains($oldSQLServerName))
 {
 $newConnectionString = $oldConnectionString.Replace($oldSQLServerName, $newSQLServerName)
 $property.'#text' = "$newConnectionString"
 }
 # If the connection string contains the old DB2 Server name, replace with new DB2 Server name
 elseif ($oldConnectionString.Contains($oldDB2ServerName))
 {
 $newConnectionString = $oldConnectionString.Replace($oldDB2ServerName, $newDB2ServerName)
 $newConnectionString = $newConnectionString.Replace("IBMDADB2.1", "IBMDADB2.DB2COPY1")
 $property.'#text' = "$newConnectionString"
 }
 # If the connection string contains the old file path, replace old file path with new file path
 elseif ($oldConnectionString.Contains($oldFileConnectionPath))
 {
 $property.'#text' = $newFileConnectionPath
 }
 }
 } 
 }
 }
 }
 
 function ChangeProtectionLevel($level)
 {
 $protectionLevelNode = $xml.SelectSingleNode('//DTS:Property[@DTS:Name="ProtectionLevel"]', $ns)
 $protectionLevelNode.'#text' = $level
 }
 
 function DeletePackagesFromMSDB
 {
 $formatDelCmd = "C:\""Program Files (x86)""\""Microsoft SQL Server""\90\DTS\Binn\dtutil.exe /SQL ""{0}"" /DELETE /SourceS {1}"
 
 $packages = dir $newSSISFolderPath*.dtsx | select -ExpandProperty Basename
 
 foreach ($package in $packages)
 {
 # Delete package from MSDB
 $cmd = [string]::Format($formatDelCmd, "$MSDBFolderName\$package", $destinationSQLServerName)
 cmd /c $cmd
 }
 }
 
 function LoadPackagesIntoMSDB
 {
 $formatAddCmd = "C:\""Program Files (x86)""\""Microsoft SQL Server""\90\DTS\Binn\dtutil.exe /file ""{0}"" /DestServer {1} /COPY SQL;""{2}"" /QUIET"
 
 $packages = dir $newSSISFolderPath*.dtsx | select -ExpandProperty Basename
 
 foreach ($package in $packages)
 {
 # Load package into MSDB
 $cmd = [string]::Format($formatAddCmd, "$newSSISFolderPath$package.dtsx", $destinationSQLServerName, "$MSDBFolderName\$package")
 cmd /c $cmd
 }
 }
 
 # ***************************************************************************************************************************************
 
 
 # Initialize variables which store folder path where SSIS pacakges are to be copied to local file system
 if ($MSDBFolderName)
 {
 $SSISFolderPath = "C:\temp\$sourceSQLServerName\$MSDBFolderName\"
 $newSSISFolderPath = "C:\temp\$sourceSQLServerName\" + $MSDBFolderName + "\New\"
 }
 else
 {
 $SSISFolderPath = "C:\temp\$sourceSQLServerName\"
 $newSSISFolderPath = "C:\temp\$sourceSQLServerName\New\"
 }
 
 
 # Make directory to store modified SSIS packages
 if(!(test-path -path $newSSISFolderPath))
 {
 mkdir $newSSISFolderPath | Out-Null
 }
 else
 {
 Remove-Item $newSSISFolderPath -Recurse
 mkdir $newSSISFolderPath | Out-Null
 }
 
 
 # Call function to copy SSIS packages stored on SQL Server in MSDB to local file system
 CopySSISPackagesFromMSDBToFileSystem
 
 
 # Get SSIS packages in specified folder
 $packages = dir $SSISFolderPath*.dtsx | select -ExpandProperty Fullname
 
 
 # Loop through packages in folder and update connections
 foreach ($package in $packages)
 {
 $xml = [xml] (Get-Content $package);
 
 $ns = New-Object Xml.XmlNamespaceManager $xml.NameTable;
 $ns.AddNamespace("DTS", "www.microsoft.com/SqlServer/Dts");
 
 $nodes = $xml.SelectNodes("//DTS:ConnectionManager", $ns)
 
 # Update Connections in SSIS package
 UpdateConnections($nodes)
 
 # Change protection level for a specific package to 0 - DontSaveSensitive
 if ($package.Contains("[ENTER PACKAGE NAME HERE].dtsx"))
 {
 ChangeProtectionLevel("0")
 }
 
 # Save modified package to new file
 $newPackageName = $package.Replace($SSISFolderPath, $newSSISFolderPath)
 Set-Content $newPackageName $xml.OuterXml
 }
 
 
 DeletePackagesFromMSDB
 
 LoadPackagesIntoMSDB