Capture Information From Octopus Deploy

Aim: Increase logging generated as part of database deployment via Octopus to assist when investigating failed deployments & reviewing successful deployments.

In a previous post on deploying databases using TeamCity & Octopus, I used a PowerShell script (Deploy.ps1) to deploy the databases. However, I’m not entirely happy with the information available about each deployment after it has completed (either successfully or otherwise). To assist with investigating failed deployments, I’m going to add some more logging to the script.

Update the script to the following.

# Set params
$buildName = "Accounts"
$DatabaseName = "Accounts"

Write-Host "This build will deploy: " $buildName " to Server: " $ServerName

try {
# Load in DAC DLL (requires config file to support .NET 4.0)
Add-Type -path "C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\Microsoft.SqlServer.Dac.dll"

# Make DacServices object
$d = New-Object Microsoft.SqlServer.Dac.DacServices "server=$ServerName; ;User ID=OctopusLogin; Password=password";

# Register events (this will write info messages to the Task Log)
Register-ObjectEvent -in $d -EventName Message -Source "msg" -Action { Out-Host -in $Event.SourceArgs[1].Message.Message} | Out-Null

# Get dacpac file
$dacpac = (Get-Location).Path + "\Content\Deploy\" + $buildName + ".dacpac"

# Load dacpac from file & deploy to database
$dp = [Microsoft.SqlServer.Dac.DacPackage]::Load($dacpac)

# Set the DacDeployOptions
$options = New-Object Microsoft.SqlServer.Dac.DacDeployOptions -Property @{
   'BlockOnPossibleDataLoss' = $true;
   'DropObjectsNotInSource' = $false;
   'ScriptDatabaseOptions' = $true;
}

# Generate the deployment script
$deployScriptName = $buildName + ".sql"
$deployScript = $d.GenerateDeployScript($dp, $DatabaseName, $options)

# Write the script out to a file
$deployScript | Out-File $deployScriptName

# Deploy the dacpac
$d.Deploy($dp, $DatabaseName, $true, $options)

# Clean up event
Unregister-Event -Source "msg"

exit 0 # Success
}
catch {
# Called on terminating error. $_ will contain details
exit 1 # Failure
}

The following section will generate & write the deployment script to Account.sql file in D:\Octopus\Applications\Local\Accounts\<release number> on the Tentacle server.

# Generate the deplopyment script
$deployScriptName = $buildName + ".sql"
$deployScript = $d.GenerateDeployScript($dp, $DatabaseName, $options)

# Write the script out to a file
$deployScript | Out-File $deployScriptName

To write the deployment script to the Task Log, you can add the following section. But this may be quite long & is not very useful when investigating a failed deploy.

# Return the script to the log
Write-Host $deployScript

A better option (in my opinion) is to write out the details of each action performed when the script is executed, e.g. “Creating [dbo].[Customer]…”, to the task log. This is done by the following section:

# Register events
$r = Register-ObjectEvent -in $d -EventName Message -Source "msg" -Action { Out-Host -in $Event.SourceArgs[1].Message.Message} | Out-Null

The try-catch will ensure that if an actual error (rather than information message) is raised, the script will exit with an exit code greater than 0 so that Octopus & TeamCity both recognise it as a failed deployment.

With these changes to the script in place, the output in the Octopus Task Log for a successful deploy is:

And the output for a failed deploy, e.g. when trying to remove a column from a table that contains data, is:

---

Useful links:
Details on Register-ObjectEvent can be found here: http://technet.microsoft.com/en-us/library/hh849929.aspx & Unregister-Event can be found here: http://technet.microsoft.com/en-us/library/hh849896.aspx.
Note: It is important to unsubscribe using Unregister-Event at the end of the script. Otherwise, it will fail the next time it is run as it will try to register a subscriber that already exists.