Creating Octopus Templates*

*Also known as “how to miss the obvious”

I have been doing a lot of work with Octopus lately, as our company has started to use it for automated deployment of projects to our staging and UAT environments. Unfortunately, a lot of live deployments to customers require sending packages for them to deploy, rather than us deploying automatically also through Octopus.

But Octopus allows you to write custom templates, in Powershell, which is a big help to developing your own steps to do just what you need.

The project I am on at the moment requires us to send SQL upgrade scripts to the customer as part of the deployment. We have included a database project to the Visual Studio solution, allowing us much better management of any changes. We can use the Dacpac generated by this to create a SQL Upgrade script. But we wanted to keep track of all these scripts. And so I started on a new custom step template.

When I write these, I always start with just a plain Powershell .ps1 script. I set out the parameters I need passing through, then include the Octopus parameter syntax below it.

param (
   [Parameter(Position=0, Mandatory=$true)][string]$DatabaseServer,
   [Parameter(Position=1, Mandatory=$true)][string]$DatabaseName,
   [Parameter(Position=2, Mandatory=$true)][string]$UserId,
   [Parameter(Position=3, Mandatory=$true)][string]$Password,
   [Parameter(Position=4, Mandatory=$true)][string]$UpgradeScript,
   [Parameter(Position=5, Mandatory=$true)][string]$Version
)

# $DatabaseServer = $OctopusParameters['server']
# $DatabaseName = $OctopusParameters['database']
# $UserId = $OctopusParameters['user']
# $Password = $OctopusParameters['password']
# $UpgradeScript = $OctopusParameters['upgradeScript']
# $Version = $OctopusParameters['version']

I needed to connect to SQL Server to check if a table existed, creating it if it didn’t

[void] [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")
$srv = new-Object Microsoft.SqlServer.Management.Smo.Server("$DatabaseServer")
$db = New-Object Microsoft.SqlServer.Management.Smo.Database
$db = $srv.Databases.Item("$DatabaseName")

$tb = $db.Tables['DatabaseUpdates']
if (!$tb)
{
	# Create the Table
	Write-Output "Table not found - creating"
	$tb = new-object Microsoft.SqlServer.Management.Smo.Table($db, "DatabaseUpdates")
	$col1 = new-object Microsoft.SqlServer.Management.Smo.Column($tb, "ID", [Microsoft.SqlServer.Management.Smo.DataType]::Int)
	$col1.Identity = $true
	$col2 = new-object Microsoft.SqlServer.Management.Smo.Column($tb, "RunDate", [Microsoft.SqlServer.Management.Smo.DataType]::DateTime)
	$col3 = new-object Microsoft.SqlServer.Management.Smo.Column($tb, "Version", [Microsoft.SqlServer.Management.Smo.DataType]::NVarChar(10))
	$col4 = new-object Microsoft.SqlServer.Management.Smo.Column($tb, "PathToUpgradeScript", [Microsoft.SqlServer.Management.Smo.DataType]::NVarChar(255))
	$tb.Columns.Add($col1)
	$tb.Columns.Add($col2)
	$tb.Columns.Add($col3)
	$tb.Columns.Add($col4)
	$tb.Create()
}
else
{
	Write-Output "Table already exists"
}

Note the need to load an assembly in order for this to work.

Then I needed to update the table to keep a track of each upgrade. And this is where my brain scrambled because the easy code that I thought would work:

# Add a new entry to the table
$sql = "insert into DatabaseUpdates ([RunDate],[Version],[PathToUpgradeScript]) VALUES (GETDATE(),{0},{1})" -f $Version,$UpgradeScript

$Connection = New-Object System.Data.SQLClient.SQLConnection
$Connection.ConnectionString = "Server={0};Database={1};User ID={2};Password={3};Trusted_Connection=False;Connect Timeout=15" -f $srv,$db,$UserId,$Password
$Connection.Open()

$Command = New-Object System.Data.SQLClient.SQLCommand
$Command.Connection = $Connection
$Command.CommandText = $sql
$Command.ExecuteReader()

$Connection.Close()

gave me an error:-

I spent a frustrating hour trying to work out why the connection wouldn’t work and then had a complete {facepalm} moment – I was using the objects created by the first step $srv & $db to try and create the connection. Not the string values I should have been using from the parameters.

# Add a new entry to the table
$sql = "insert into DatabaseUpdates ([RunDate],[Version],[PathToUpgradeScript]) VALUES (GETDATE(),{0},{1})" -f $Version,$UpgradeScript

$Connection = New-Object System.Data.SQLClient.SQLConnection
$Connection.ConnectionString = "Server={0};Database={1};User ID={2};Password={3};Trusted_Connection=False;Connect Timeout=15" -f $DatabaseServer,$DatabaseName,$UserId,$Password
$Connection.Open()

$Command = New-Object System.Data.SQLClient.SQLCommand
$Command.Connection = $Connection
$Command.CommandText = $sql
$Command.ExecuteReader()

$Connection.Close()

Proof that some of the most frustrating errors are usually caused by the simplest of errors!

Adding this (now working) script to Octopus is very straightforward. Go to Library > Script templates and click the ‘Add Step Template’ button. Choose the ‘Run a Powershell script’ from the options supplied. From here you have 3 sections to complete.

Settings

For the name and description of your step.

Parameters

Where you define the parameters to be passed through. The ‘variable name’ value is how you reference it within your script.

Step

Where you copy your Powershell script, making sure you remove the parameters section and using the Octopus parameters instead.

Before you jump into creating your own step templates, I’d recommend checking out the Community Library where other users have added generic scripts that could be useful i.e. create directories, run an installer, etc.