DeployTSQLScripts

DeployTSQLScripts.ps1

### 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!";