Applying custom settings with Octopus Deploy

If you’re a .Net developer and not currently doing automated deployments then you really should be. If you’d like some help getting started send me a message in the comments and I’ll drop you an email.

The core of any deployment is the ability to easily manage and apply configuration values, across environments and across your different apps/web apps.

Octopus Deploy has first class support for managing variables, including setting different scopes or environments and steps in your deployment process. It also has first class support for substituting variables into your <appSettings> and <connectionStrings>. In this case, all you need to do is create a variable with the same name as the appSetting and Octopus Deploy will swap in this value during deployment. Easy.

So what’s the problem?

The trouble comes when you start using custom settings in your app.config and web.config files. For example, you might have a web.config file that looks like this:

    <section name="SomeCrazySettings" type="SomeAssembly.SomeCrazySettings, SomeAssembly" />
    <!-- omitted -->
    <CustomThing key1="foo" key2="bar" />

Octopus Deploy does have a built in way of dealing with this problem, but the downside is that you have to hard-code the values in the Octopus Deploy convention, e.g.

  <CustomThing key1="#{Crazy Key1}" key2="#{Crazy Key2}" /> 

The problem that I have with the above is when I want to hard-code the values for local development. One of the applications I work on has a config item called BaseUri and when I’m working locally I like to have it set to my local machine e.g. http://localhost:44301. So the syntax above doesn’t work unless I’m happy with constantly changing the values.

In this blog post I’m going to show how with a little bit of PowerShell we can update the custom settings shown above.

Deployment Script

PowerShell makes your deployments awesome, especially in the context of Octopus Deploy. Anything that isn’t baked in can easily be implement with a little bit of PowerShell.

$Path = $OctopusParameters["OctopusOriginalPackageDirectoryPath"]

$Config = Get-ChildItem "$Path\*" -Include *.exe.config | Select-Object -First 1
[xml]$ConfigXml = Get-Content $Config

$configNodes = $ConfigXml.SelectNodes("//SomeCrazySettings");

if ($configNodes.Count -eq 1)


This script does the following:

  • Line 1-4: Finds the config file and then loads it as an XML object
  • Line 6: Using XPath looks for the node with the custom configuration
  • Line 8-14: If the node was found, updates the values of the custom keys, and then saves the file.

Go forth and automate.


TeamCity – Branching Databases and Continuous Integration

This week there have been lots of build failures, thanks to our HipChat and TeamCity integration I’m notified constantly. As a “senior developer”, this matters to me. The problem comes down to multiple feature branches using a single CI database, which means that it isn’t always “up-to date”, hence the failing tests.


In this post I’m going to show you how I fixed this by setting up TeamCity to publish a database for each of our feature branches.

There are a two bits that need to be done to achieve this end goal:

  1. TeamCity needs to publish the SQL Server Database Project into a separate database for each branch
  2. All connection strings throughout the solution need to be updated to point to this new database

Publishing the SQL Database Project

To accomplish this we will be using the SqlPackage.exe command line utility (MSDN docs) which is shipped with SQL Server Data Tools (you will have probably already installed this on your build server to get it to build the sqlproj). This command line tool does the same thing as when you right click in Visual Studio on your project and select publish.

We can use SqlPackage.exe to publish a new database by using the following command:


The arguments are pretty self-explanatory, except for perhaps the last one. The profile as you will see in a minute allows you to define different parameters in an XML file which are then used during deployment.

Creating the Continuous Deployment Profile

For our CI environment deployments I always want the database to be dropped and recreated, this can be configured in the deployment profile.

In Visual Studio, right click on your database project and click Publish. This will bring up the Publish Database window.


Then click Advanced, this will bring up the Advanced Publish Settings. Tick “Always re-create database”. The options you can select are extensive and worth a look through.


Click OK to close this and go back to the Publish Settings window.

To create the Profile simply click the “Create Profile” button, this will add a new {projname}.publish.xml file to your project.


I’ve renamed mine to Database.CI.publish.xml

Setting up TeamCity

The next thing to do is to have TeamCity actually publish the database project. In TeamCity add a new Build step:

  1. Runner type: Command Line
  2. Step name: Publish database
  3. Run: Executable with parameters
  4. Command executable: C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\SqlPackage.exe
  5. Command parameters:


Then click Save.


Updating Connection Strings

Once TeamCity has built the solution and published the latest version of the database I needed a way to update all the connection strings in the various app.config and web.config files.

To accomplish this I put together the following PowerShell script:

$connectionStringName = $args[0];
$newConnectionString = $args[1];

# get the directory of this script file
$currentDirectory = [IO.Path]::GetDirectoryName($MyInvocation.MyCommand.Path)

$fileList = gci $currentDirectory -Recurse -Filter *.config

foreach($file in $fileList)
    $appConfig = New-Object XML
    # load the config file as an xml object

    foreach($connectionString in $appConfig.configuration.connectionStrings.add)

        if ($ -eq $connectionStringName)
            'file: ' + $file.FullName
            # write the name to the console
            'name: ' + $
            # write the connection string to the console
            'connectionString: ' + $connectionString.connectionString
            # change the connection string
            $connectionString.connectionString = $newConnectionString
    # save the updated config file

What this does is it will find all *.config files files recursively from the directory that the PowerShell script lives in, it will then load each config file as an XML file and find all the connection strings, any that have a name that matches the first argument will have the ConnectionString property updated to the value of the second argument.

To configure TeamCity add a new build step:

  1. Runner type: Powershell
  2. Step name: Update connection strings
  3. Powershell run mode: Version 2.0; Bitness: x86
  4. Script: File
  5. Script file: ConnectionStringUpdate.ps1
  6. Script execution mode: Execute .ps1 script with “-File” argument
  7. Script arguments: “MyDatabaseConnString” “;Server=…”


Back into the green

Now when code is pushed up to BitBucket TeamCity will run through the following steps:

  1. Build the solution
  2. Publish the database project(s)
  3. Update connection strings
  4. Run unit test / integration tests – currently one step

TeamCity is back to being happy


and more importantly, when the build completes HipChat notifies me that the build has been