How to capture DacSevices.Deploy output?

Question

So I've managed to deploy our DACPAC schema via Octopus. I'm using a Deploy.ps1 script interacting with .Net objects just like the article describes.

I'd like to make the deployment process more transparent by including the "standard output" you get from sqlcmd in our Octopus logs. I'm looking for the the generated schema modification messages as well as any custom migration migration messages our developers have put into the pre/post scripts.

The only workaround I can think of is to first generate the script with the DACPAC services and then run it with sqlcmd.exe. Any ideas?

Salyers

Answer 1

Found the solution, posting in case someone else runs across this. You simply need to subscribe to the your DacService's Message event.

C# sample:

var services = new Microsoft.SqlServer.Dac.DacServices("data source=machinename;Database=ComicBookGuy;Trusted_connection=true");

var package = Microsoft.SqlServer.Dac.DacPackage.Load(@"C:\Database.dacpac");

var options = new Microsoft.SqlServer.Dac.DacDeployOptions();
options.DropObjectsNotInSource = true;
options.SqlCommandVariableValues.Add("LoginName", "SomeFakeLogin");
options.SqlCommandVariableValues.Add("LoginPassword", "foobar!");

services.Message += (object sender, Microsoft.SqlServer.Dac.DacMessageEventArgs eventArgs) => Console.WriteLine(eventArgs.Message.Message);

services.Deploy(package, "ComicBookGuy", true, options);

Powershell sample (executed by the Octopus Tentacle):

# This script is run by Octopus on the tentacle
$localDirectory = (Get-Location).Path
$tagetServer = $OctopusParameters["SQL.TargetServer"]
$databaseName = "ComicBookGuy"

Add-Type -path "$localDirectory\lib\Microsoft.SqlServer.Dac.dll"

$dacServices = New-Object Microsoft.SqlServer.Dac.DacServices ("data source=" + $tagetServer + ";Database=" + $databaseName + "; Trusted_connection=true")
$dacpacFile = "$localDirectory\Content\Unity.Quotes.Database.dacpac"

$dacPackage = [Microsoft.SqlServer.Dac.DacPackage]::Load($dacpacFile)

$options = New-Object Microsoft.SqlServer.Dac.DacDeployOptions
$options.SqlCommandVariableValues.Add("LoginName", $OctopusParameters["SQL.LoginName"])
$options.SqlCommandVariableValues.Add("LoginPassword", $OctopusParameters["SQL.LoginPassword"])
$options.DropObjectsNotInSource = $true

Register-ObjectEvent -InputObject $dacServices -EventName "Message" -Action { Write-Host $EventArgs.Message.Message } | out-null

$dacServices.Deploy($dacPackage, $databaseName, $true, $options)

In the powershell version I couldn't get the handy "Add_EventName" style of event notification working so I had to use the clunky cmdlet. Meh.

Salyers

I just tried and it works as expected using C# and DacPac assembly. Great answer! – Louden

Worth noting that Register-ObjectEvent works asynchronously, hence, main script will not wait for Register-ObjectEvent line to process all messages and might finish before displaying all events. – Watermelon

Unlike the SqlPackage.exe, when run using powershell even when registering these events, they only seem to get reported in StdOut in one go at the end, rather than in realtime. This means that if you are waiting on a long running postdeploy, it isn't clear which one is being run. – Guanine

Answer 2

Use sqlpackage instead of sqlcmd to deploy dacpac.

Get Latest version here : https://msdn.microsoft.com/en-us/mt186501

$sqlpackage = "C:\Program Files (x86)\Microsoft Visual Studio 12.0\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\120\sqlpackage.exe"

It will automatically output errors on the console. We use TFS build definition and call powershell and it is able to display errors that happened during a deploy.

Usage:

& $sqlpackage /Action:Publish /tsn:$dbServer /tdn:$database /sf:$mydacpac/pr:$dbProfile /variables:myVariable=1

Levo

That is a smart alternative I went with the API/Powershell route instead of calling an EXE because it required a slightly smaller set of dependencies... although in retrospect the extra complexity required in the script would outweigh having the EXE around. Thanks for the input! – Salyers

David, Yes API works well too. We use it when we execute a script from the backend but we primarily use self-serve deploys in lower environments and TFS Build definitions are able to capture the errors properly. Running EXE directly from powershell does not do a good job. – Levo

This is a great solution! – Lesleelesley

Answer 3

This variation captures output but also allows you to capture and react to deploy failures by catching the exception

function Load-DacPacAssembly()
{
    $assemblyName = "Microsoft.SqlServer.Dac.dll"
    $packageFolder = <some custom code to find our package folder>
    $dacPacAssembly = "$packageFolder\lib\net46\$assemblyName"

    Write-Host "Loading assembly $assemblyName"
    Add-Type -Path "$dacPacAssembly" 
}

function Publish-Dacpac($dacpac, $publishProfile){

    Load-DacPacAssembly

    Write-Host "Loading profile $publishProfile..."
    $dacProfile = [Microsoft.SqlServer.Dac.DacProfile]::Load($publishProfile)
    $dacService = New-Object Microsoft.SqlServer.dac.dacservices ($dacProfile.TargetConnectionString)

    Write-Host "Loading dacpac $dacpac"
    $dacPackage = [Microsoft.SqlServer.Dac.DacPackage]::Load($dacpac)

    $event = Register-ObjectEvent -InputObject $dacService -EventName "Message" -Action { 
        $message = $EventArgs.Message
        $colour = "DarkGray"
        if ($message -contains "Error SQL")
        {
            $colour = "Red"
        }

        Write-Host $message -ForegroundColor $colour
    }

    Write-Host "Publishing...."

    try {
        $dacService.deploy($dacPackage, $dacProfile.TargetDatabaseName, $true, $dacProfile.DeployOptions)
    }
    catch [Microsoft.SqlServer.Dac.DacServicesException]
    {        
        $message = $_.Exception.Message
        Write-Host "SQL Publish failed - $message" -ForegroundColor Red # Customise here for your build system to detect the error
        exit;
    }
    finally
    {
        Unregister-Event -SourceIdentifier $event.Name
    }
}

Polydeuces