Scripts for building database project in Visual Studio and generate DacPac to create update scripts

SqlBuildScripts.ps1

# "C:\Program Files (x86)\Microsoft Visual Studio\2017\Professional\MSBuild\15.0\Bin"
# "C:\Program Files (x86)\Microsoft SQL Server\140\DAC\bin\"


# Taken from psake https://github.com/psake/psake

<#
.SYNOPSIS
  This is a helper function that runs a scriptblock and checks the PS variable $lastexitcode
  to see if an error occcured. If an error is detected then an exception is thrown.
  This function allows you to run command-line programs without having to
  explicitly check the $lastexitcode variable.
.EXAMPLE
  exec { svn info $repository_trunk } "Error executing SVN. Please verify SVN command-line client is installed"
#>
function Exec
{
    [CmdletBinding()]
    param(
        [Parameter(Position=0,Mandatory=1)][scriptblock]$cmd,
        [Parameter(Position=1,Mandatory=0)][string]$errorMessage = ($msgs.error_bad_command -f $cmd)
    )
    & $cmd
    if ($lastexitcode -ne 0) {
        throw ("Exec: " + $errorMessage)
    }
}

function Get-Exe
{
    [CmdletBinding()]
    param(
        [Parameter(Position=0, Mandatory=1)][string]$name
    )
   $value = [Environment]::GetEnvironmentVariable($name)
   if(-not $value)
   {
        throw ("Missing environment variable for '$name'")
   }
   $value
}

function Generate-DacPac
{
    [CmdLetBinding()]
    param(
        [Parameter(Position=0,Mandatory=1)][string]$project,
        [Parameter(Position=1,Mandatory=1)][string]$outDir
    )
    $msbuildExe = Get-Exe "msBuildExe" #Join-Path $msbuildPath "MSBuild.exe"    
    $msbuildArgs = @($project,"/t:Build","/p:Configuration=Release", "/p:TargetFramework=v4.6.1", "/p:OutDir=$outDir")
    exec { & $msbuildExe $msbuildArgs }
}

function Generate-Scripts 
{
[CmdLetBinding()]
    param(
        [Parameter(Position=0,Mandatory=1)][string]$sourceFile,
        [Parameter(Position=1,Mandatory=1)][string]$targetDatabaseName,
        [Parameter(Position=2,Mandatory=1)][string]$targetServerName,
        [Parameter(Position=3,Mandatory=1)][string]$outDir
    )    
    $sqlPackageExe = Get-Exe "sqlPackageExe"
    Write-Debug "Generating scripts using SqlPackage at: $sqlPackageExe"
    # Deploy script path    
    $dsp = Join-Path $outDir 'Deploy.sql'
    # Deploy report path
    $drp = Join-Path $outDir 'Report.xml'
    $sqlPackageArgs = @(
        "/a:Script", 
        "/sf:$sourceFile", 
        "/tdn:$targetDatabaseName",
        "/tsn:$targetServerName",
        "/dsp:$dsp",
        "/drp:$drp")
    exec { & $sqlPackageExe $sqlPackageArgs }
}


function Init
{
[CmdLetBinding()]
    param(
        [Parameter(Position=0,Mandatory=1)][string]$sqlProj,
        [Parameter(Position=1,Mandatory=1)][string]$outDir,
        [Parameter(Position=2,Mandatory=1)][string]$targetServerName,
        [Parameter(Position=3,Mandatory=1)][string]$targetDatabaseName
    ) 
    $sqlProjectFile = Get-Item $sqlProj
    Generate-DacPac $sqlProj $outDir
    $dacpac = Join-Path $outDir "$($sqlProjectFile.BaseName).dacpac"
    Generate-Scripts $dacpac $targetDatabaseName $targetServerName $outDir
}

# Remove parameters for prompt to appear.
Init 'path/to/sql/project' `
    'path/to/outdir' `
    'servername' `
    'databasename'