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:
1 octopus_task_log_success
And the output for a failed deploy, e.g. when trying to remove a column from a table that contains data, is:
2 octopus_task_log_failure

Useful links:
Details on Register-ObjectEvent can be found here: & Unregister-Event can be found here:
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.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Kevin Kline

Career and Technical Advice for the IT Professional


Explaining the bits and bytes of SQL Server and Azure

SQL Authority with Pinal Dave

SQL Server Performance Tuning Expert


A blog about PowerShell and general Windows sysadmin stuff

Simon Learning SQL Server

I'm trying to become "better" at SQL Server and data - here's how I'm doing it!

%d bloggers like this: