PostDeploy script for Octopus that executes SQL

PostDeploySql.ps1

trap {
    Write-Error "$_"
    Exit 1
}

# Injected by Octopus Deploy Step Template
$connectionString = $DeploySqlConnectionString
$filter = $Filter

Write-Host "Executing scripts in folder '$($PSScriptRoot)'"
$scripts = Get-ChildItem -Recurse -Filter $filter | ForEach-Object { (Get-Item $_.FullName) }

# Open Connection
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString

Write-Host "Connecting to '$connectionString'"

$continueOnError = $true

Register-ObjectEvent -InputObject $connection -EventName InfoMessage -Action {
    Write-Host "[Event] $($Event.SourceEventArgs)"
} | Out-Null

function Invoke-SqlQuery($fileName)
{
    Write-Host "Executing $((Get-Item $filename).Name)"

    $content = Get-Content $fileName -raw
    $queries = [System.Text.RegularExpressions.Regex]::Split($content, '\r\n\s*GO\s*\r\n', [System.Text.RegularExpressions.RegexOptions]::IgnoreCase) | Where-Object { $_ -ne '' }

    foreach($q in $queries)
    {
        if ((-not [String]::IsNullOrWhiteSpace($q)) -and ($q.Trim().ToLowerInvariant() -ne "go"))
        {
            $command = $connection.CreateCommand()
            $command.CommandText = $q
            $command.CommandTimeout = $paramCommandTimeout
            $command.ExecuteNonQuery() | Out-Null
        }
    }
}

try {
    $connection.Open() | Out-Null
    foreach ($filename in $scripts) {
        Invoke-SqlQuery $filename
    }
} catch {
    if ($continueOnError) {
        Write-Host $_.Exception.Message
    } else {
        throw
    }
} finally {
    $connection.Dispose() | Out-Null
}