Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to capture DacSevices.Deploy output?

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?

like image 559
David Peters Avatar asked Jul 10 '15 19:07

David Peters


2 Answers

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.

like image 124
David Peters Avatar answered Nov 17 '22 01:11

David Peters


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
like image 31
Vinay Avatar answered Nov 17 '22 01:11

Vinay