Packing Files
This is one of those write it down so you don’t forget posts. Because I already didn’t write it down once and I am having to flail/dig around to my head wrapped around it.
Nearly every database project I have currrently has a number of supplementary scripts (99% of the time these are custom Octopus Deploy steps). Up until now we’ve made do with keeping them in the project repo but copy/pasting them into Octopus when needed. It’s only bitten us a couple of times…
Octopus Deploy released features recently that let you point to a script directly in a Github repo. In trying to get this feature working it became apparent that maybe I needed to package it up (the released feature seems to tie the step to a specific commit rather than the most recent version of the file in a branch etc.).
Which brought me around to solving the problem of how do I package up files. I am not really a developer although I yell drink about them a lot. We use Redgate SQL Source Control or SQL Change Automation which–to date–have handled creating the packages that are needed by our continuous delivery processes (i.e. Octopus) so I was flailing a lot until I trapped one of the developers at work and made him be my duck (I really needed one that could talk back).
The following pieces are what I’ve cobbled together to package up supplementary scripts using Github actions and nuget.
Editor’s note: I initially was looking into using more formal build processes and use a nuget package but this was overkill/uncessary: in a github action we just dump all the relevant files in to a zip file and call it good.
After some amount of flailing I eventually determined that there was no reason to try and force a nuget package and that so long as we had the relevant PowerShell scripts in a zip file we’d have everything that we need. Below is the YAML for the workflow that is used for any project that includes PowerShell scripts to be used in Octopus Deploy steps (either as custom steps or in step templates). If a step will be resused in multiple projects we create a step template and point it a the appropriate package and configure the step parameters etc. If the script is a one-off script for a specific project we’ll just reference the script in the runbook or deployment process as needed. The action runs on a self hosted runner so it can push the package to our locally hosted Octopus Deploy server. It does reference a custom action step that we are using to use that checks for and creates a version tag for use in the package name. And another action that cycles through any projects where the package is in use and checks for runbooks that need to be republished: this was important as Octopus Deploy does not at this time expose the publish button if the only “change” is an updated package for any steps.
The job definition:
# This action should generate a package to be pushed to octopus deploy
# when there are changes to Octopus Step scripts (templates or inline scripts)
name: "Generate PS Scripts Package"
on:
# Triggers the workflow on push or pull request events but only
# for the "release" branch
push:
branches: [release]
# we only need new versions/packages
paths:
- OctopusDeployTemplates/**
- Actions/**
workflow_dispatch:
env:
# publish Octopus Scripts to the General_Powershell package.
project-name: "General_PowerShell"
defaults:
run:
shell: pwsh
jobs:
set_config:
name: Set Build Configuration
runs-on:
group: organization/SQL
labels: [self-hosted, Windows]
outputs:
build-id: ${{ steps.update_version.outputs.build-id }}
artifact-name: ${{ steps.update_version.outputs.artifact-name }}
permissions:
# this runner will add labels/tags to commits and pull requests
contents: write
steps:
# Checks-out your repository under $GITHUB_WORKSPACE, so your job can access it
- id: update_version
name: "Update Version Number"
uses: <custom action repo>/Actions/[email protected]
with:
# for PS packages append _PowerShell to the project name:
project-name: "${{ env.project-name }}"
include-branch: false
create_package:
name: Create and Push Package 🐙
needs: set_config
runs-on:
group: organization/SQL
labels: [self-hosted, Windows]
steps:
- name: Push Package
uses: <custom action repo>/Actions/[email protected]
with:
artifact-name: ${{ needs.set_config.outputs.artifact-name }}
build-identifier: ${{ needs.set_config.outputs.build-id}}
octopus-api-key: ${{ secrets.DB_OCTOPUS_KEY }}
octopus-server: ${{ secrets.DB_OCTOPUS_URL }}
# append _PowerShell for PS packages...
project-name: "${{ env.project-name }}"
source-folder: "./OctopusDeployTemplates"
# updating PS scripts should not trigger Octopus Build:
create-release: false
update_runbooks:
name: Update Runbooks 🐙
needs: [set_config, create_package]
runs-on:
group: organization/SQL
labels: [self-hosted, Windows]
steps:
- name: Update Runbooks
uses: <custom action repo>/Actions/[email protected]
with:
octopus-url: ${{ secrets.DB_OCTOPUS_URL }}
octopus-key: ${{ secrets.DB_OCTOPUS_KEY }}
project-name: ""
This YAML defines a custom action that will republish any runbooks that include the package we just updated. I’ve tweaked the PowerShell in this step but it originally came from the Octopus Deploy forum (I’ve lost the link and cannot seem to locate the article/source again).:
# T# this action will cycle through all runbooks in a project and republish runbooks
# that include the passed in package.
name: Republish Runbooks With Updated Packages
inputs:
octopus-url:
description: "The URL of the Octopus Deploy server."
require: true
octopus-key:
description: "The API key for accessing the Octopus Deploy server."
require: true
project-name:
description: "Comma separated list of projects that can be cycled through."
space-name:
description: "The name of the space on the Octopus Deploy server."
default: 'Default'
runs:
using: "composite"
steps:
- name: Republish Runbooks with updated Package(s)
shell: pwsh
run: |
$projectsList = "${{ inputs.project-name }}".Split(',') | Where-Object {$_ -ne ""}
$ErrorActionPreference = "Stop";
# Define working variables
$octopusURL = "${{ inputs.octopus-url }}"
$header = @{ "X-Octopus-ApiKey" = "${{ inputs.octopus-key }}" }
$spaceName = "${{ inputs.space-name }}"
# Get space
$spaces = Invoke-RestMethod -Uri "$octopusURL/api/spaces?partialName=$([uri]::EscapeDataString($spaceName))&skip=0&take=100" -Headers $header
$space = $spaces.Items | Where-Object { $_.Name -eq $spaceName }
# if $projectsList is null then get _all_ projects from Octopus:
if ($null -eq $projectsList) {
$returnedList = Invoke-RestMethod -Uri "$octopusURL/api/$($space.Id)/projects?skip=0&take=100" -Headers $header
$projectsList = $returnedList.Items | Select-Object -ExpandProperty Name
}
foreach($projectName in $projectsList){
$projects = Invoke-RestMethod -Uri "$octopusURL/api/$($space.Id)/projects?partialName=$([uri]::EscapeDataString($projectName))&skip=0&take=100" -Headers $header
Write-Output "Processing runbooks in $($projectName)."
$project = $projects.Items | Where-Object { $_.Name -eq $projectName }
# Get runbooks
try{
# skip if project is disabled
if ($project.IsDisabled) {
$runbooks = $null
}
else {
$runbooks = Invoke-RestMethod -Uri "$octopusURL/api/$($space.Id)/projects/$($project.Id)/runbooks?skip=0&take=100" -Headers $header
}
}
catch{
Write-Output "Error attempting to return runbooks for $projectName."
$runbooks = $null
}
$runbooksNeedingNewSnapshot = @()
if ($null -ne $runbooks){
foreach ($runbook in $runbooks.Items) {
Write-Host "Working on runbook: $($runbook.Name)"
if ($null -ne $runbook.PublishedRunbookSnapshotId) {
# Get the runbook snapshot
try {
$runbookSnapshot = (Invoke-RestMethod -Method Get -Uri "$octopusURL/api/$($space.Id)/runbookSnapshots/$($runbook.PublishedRunbookSnapshotId)" -Headers $header)
if ($runbookSnapshot.SelectedPackages.Count -gt 0) {
# Get Snapshot template to link packages to action/step
$runbookSnapshotTemplate = Invoke-RestMethod -Uri "$octopusURL/api/$($space.Id)/runbookProcesses/$($runbook.RunbookProcessId)/runbookSnapshotTemplate?runbookSnapshotId=$($runbookSnapshot.Id)" -Headers $header
foreach ($package in $runbookSnapshot.SelectedPackages) {
# Get packageId from snapshot template
$snapshotTemplatePackage = $runbookSnapshotTemplate.Packages | Where-Object { $_.StepName -eq $package.StepName -and $_.ActionName -eq $package.ActionName -and $_.PackageReferenceName -eq $package.PackageReferenceName } | Select-Object -First 1
if ($null -ne $snapshotTemplatePackage) {
$snapshotPackageVersion = $package.Version
# Get latest package version from built-in feed.
$packages = Invoke-RestMethod -Uri "$octopusURL/api/$($space.Id)/feeds/$($snapshotTemplatePackage.FeedId)/packages/versions?packageId=$($snapshotTemplatePackage.PackageId)&take=1" -Headers $header
$latestPackage = $packages.Items | Select-Object -First 1
if ($latestPackage.Version -ne $snapshotPackageVersion) {
Write-Host "Found package difference for $($snapshotTemplatePackage.PackageId) in runbook snapshot $($runbookSnapshot.Name)"
Write-Host "Snapshot version: $($snapshotPackageVersion), Latest package version: $($latestPackage.Version)"
$runbookDetails = @{
ProjectId = $project.Id
RunbookId = $runbook.Id
RunbookProcessId = $runbook.RunbookProcessId
RunbookSnapshotId = $runbookSnapshot.Id
RunbookName = $runbook.Name
}
$runbooksNeedingNewSnapshot += $runbookDetails
break
}
}
else {
Write-Output "Step was disabled or deleted since last time runbook was published."
}
}
}
}
catch {
Write-Output "Error getting snapshot for $($runbook.Name)."
}
}
}
}
if ($runbooksNeedingNewSnapshot.Count -gt 0) {
Write-Host "Found $($runbooksNeedingNewSnapshot.Count) runbooks which need new snapshots"
foreach ($runbookItem in $runbooksNeedingNewSnapshot) {
Write-Host "Creating new snapshot for runbook: $($runbookItem.RunbookName)"
# Get a new runbook snapshot template
try {
$runbookSnapshotTemplate = Invoke-RestMethod -Uri "$octopusURL/api/$($space.Id)/runbookProcesses/$($runbookItem.RunbookProcessId)/runbookSnapshotTemplate" -Headers $header
}
catch {
Write-Output "An error occured returning the runbook snapsho template for $($runbookItem.RunbookName) in $($projectName)"
$runbookSnapshotTemplate = $null
break
}
# Create a new runbook snapshot
$body = @{
ProjectId = $runbookItem.ProjectId
RunbookId = $runbookItem.RunbookId
Name = $runbookSnapshotTemplate.NextNameIncrement
Notes = $null
SelectedPackages = @()
}
# Include latest built-in feed packages
foreach ($package in $runbookSnapshotTemplate.Packages) {
if ($package.FeedId -eq "feeds-builtin") {
# Get latest package version
$packages = Invoke-RestMethod -Uri "$octopusURL/api/$($space.Id)/feeds/feeds-builtin/packages/versions?packageId=$($package.PackageId)&take=1" -Headers $header
$latestPackage = $packages.Items | Select-Object -First 1
$package = @{
ActionName = $package.ActionName
Version = $latestPackage.Version
PackageReferenceName = $package.PackageReferenceName
}
$body.SelectedPackages += $package
}
}
$body = $body | ConvertTo-Json -Depth 10
# Create runbook snapshot
$runbookPublishedSnapshot = Invoke-RestMethod -Method Post -Uri "$octopusURL/api/$($space.Id)/runbookSnapshots?publish=true" -Body $body -Headers $header
# Get runbook
$runbook = Invoke-RestMethod -Uri "$octopusURL/api/$($space.Id)/runbooks/$($runbookItem.RunbookId)" -Headers $header
# Publish the new snapshot
$runbook.PublishedRunbookSnapshotId = $runbookPublishedSnapshot.Id
Invoke-RestMethod -Method Put -Uri "$octopusURL/api/$($space.Id)/runbooks/$($runbook.Id)" -Body ($runbook | ConvertTo-Json -Depth 10) -Headers $header > $null
Write-Host "Published new snapshot of $($runbookItem.RunbookName): $($runbookPublishedSnapshot.Id) ($($runbookPublishedSnapshot.Name))"
}
}
}
And for completeness here is the custom action that is used to generate a release number for a package. We made the decision to make release numbers reflect the rough point in time it was created using the format YYYY.Q.Number.
# this action will increment the version tag and output the needed values to create
# a package for octopus deploy.
name: Increment Version Number
inputs:
project-name:
description: "The name of the project"
require: true
include-branch:
description: "Flag indicating if the branch name should be appended to the version tag."
default: true
outputs:
build-id:
description: "The generated version number."
value: ${{ steps.update_version.outputs.build-id }}
artifact-name:
description: "The generated zip file name to create for this build."
value: ${{ steps.update_version.outputs.artifact-name }}
runs:
using: "composite"
steps:
- uses: actions/checkout@v4
with:
fetch-tags: 'true'
fetch-depth: 100
- id: update_version
name: Update Version
shell: pwsh
run: |
# remove any lingering tags that have been removed from origin:
Write-Output "Checking if latest commit is already tagged..."
$CurrentTag = git tag --points-at HEAD
$branch = git branch --show-current
$bContinue = $true
if ($CurrentTag -match 'v\d{4}\.\d{1}\.\d+?'){
Write-Output "Tag exists ($($CurrentTag)) for commit."
$CurrentTag = $CurrentTag -replace "v", ""
$bContinue = $false
}
if ($bContinue){
$CurrentYear = (Get-Date).Year
$CurrentQuarter = [math]::Ceiling((get-Date).Month/3)
Write-Output "Retreiving the most recent tag..."
$tags = git describe --tags
if ($null -eq $tags) {
Write-Output "No tags detected: setting defaults..."
$Year = 1900
$Quarter = 1
$Build = 0
} else {
Write-Output "Tag detected: setting values..."
$tags = $tags.Split('.')
$Year = [int]($tags[0] -replace 'v', '')
$Quarter = [int]$tags[1]
if ($tags[2].IndexOf('-') -gt 0) {
$Build = [int]$tags[2].Substring(0, $tags[2].IndexOf('-'))
}
else {
$Build = [int]$tags[2]
}
}
if ($Year -lt $CurrentYear) {
Write-Output "Years don't match: Resetting all values"
$Year = $CurrentYear
$Quarter = $CurrentQuarter
$Build = 1
}
elseif ($Year -eq $CurrentYear -and $Quarter -lt $CurrentQuarter) {
Write-Output "Quarters don't match: Resetting quarter and build values."
$Quarter = $CurrentQuarter
$Build = 1
}
else {
Write-Output "Incrementing build value."
$Build += 1
}
$newTag = "v$($CurrentYear).$($CurrentQuarter).$($Build)"
if ($branch.Length -gt 0 -and $${{ inputs.include-branch }}) {
$newTag += "-$($branch)"
}
Write-Output "Adding tag: $($newTag)"
git tag $newTag
git push origin $newTag
$CurrentTag = $newTag -replace "v", ""
}
Write-Output "Setting Output Variables..."
"build-id=$CurrentTag" >> $env:GITHUB_OUTPUT
"artifact-name=${{ inputs.project-name }}.$CurrentTag.zip" >> $env:GITHUB_OUTPUT
- name: Display Outputs
shell: pwsh
run: |
Write-Output "Confirming job outputs:"
Write-Output "Project Name: ${{ inputs.project-name }}"
Write-Output "Build ID: ${{ steps.update_version.outputs.build-id }}"
Write-Output "Artifact Name: ${{ steps.update_version.outputs.artifact-name }}"