PowerShell: Find/Replace Template

Brandon had this in some of his maintenance plan code and I found myself using it a bit so I thought I'd blog about it. Here is a simple script that reads a file (C:\scripts\maintenanceplans\$sqlversion\template.dtsx), replaces certain placeholders (in this case, [SERVERNAME] and [BACKUPPATH]), then writes a new file with that content.

 1# Example values
 2$sqlVersion  = '2008R2'
 3$serverName  = 'sqlserver1'
 4$backupPath  = 'D:\dbbackups'
 5
 6# Read in the maintenance plan template and replace the placeholders
 7$textTemplate = [IO.File]::ReadAllText(".\maintenanceplans\$sqlVersion\template.dtsx")
 8$textTemplate = $textTemplate.Replace('[SERVERNAME]', $serverName)
 9$textTemplate = $textTemplate.Replace('[BACKUPPATH]', $backupPath)
10
11# Write the new package. MUST use UTF8 encoding or it will break.
12Set-Content -Path '.\out.dtsx' -Value $textTemplate -Encoding utf8

This particular script writes out XML files. If you are writing a plain text file, remove -Encoding utf8 and you're set.

Note: On newer PowerShell versions (7+), -Encoding utf8 writes UTF-8 without a BOM. If your consumer requires a BOM (many SSIS/DTX scenarios from this era did), use -Encoding utf8BOM instead.