Skip to content

Instantly share code, notes, and snippets.

@aaronsteers
Created December 4, 2017 22:40
Show Gist options
  • Save aaronsteers/8a3d080bddc10abeedc27abee7c2ad89 to your computer and use it in GitHub Desktop.
Save aaronsteers/8a3d080bddc10abeedc27abee7c2ad89 to your computer and use it in GitHub Desktop.
DeployTSQLScripts
### This script will execute any T-SQL scripts located in a named subfolder (taken as the first script argument).
### Any references to the templated schema name ("DWCI_Test") will be replaced with the value that is provided
### as the 2nd command line argument.
Param(
[string]$subFolder,
[string]$deploymentSchema
)
$ErrorActionPreference = "Stop"
$ServerName = "pssql-prod.skynet.local";
$Database = "PRODDW";
# $DeploymentSchema = "DWCI_Test" # Provided at command line
# $DefaultSchema = "DWCI_Test" # Not configurable
New-Item -ItemType Directory -Force -Path $subFolder\build;
Remove-Item $subFolder\build\*.sql;
"Compiling scripts..."
Get-ChildItem -Path $subFolder\*.sql -File | Foreach-Object {
$DestFile = $_.Directory.FullName + "\build\" + $_.Name;
(Get-Content $_.fullname) `
-replace '\[?\bDWCI_Test\b\]?', ('[' + $DeploymentSchema + ']') `
-replace 'select', 'SELECT' `
-replace 'CREATE VIEW', 'CREATE OR ALTER VIEW' |
Out-File $DestFile;
}
"Running scripts...";
Get-ChildItem -Path $subFolder\build\*.sql -File | Foreach-Object {
"Running " + $_.FullName + "...";
sqlcmd -b -N -C -S $ServerName -d $Database -E -i $_.FullName;
$success = $?
if (-not $success)
{
throw 'Script aborted. Script failed: ' + $_.Name
}
}
"Scripts executed successfully!";
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment