Variable replacement inside dacpac

stonemi's Avatar

stonemi

13 Feb, 2018 10:39 PM

I would like to use variable sets to store environment specific values and have them replaced in pre-deploy scripts inside a dacpac.
It does not appear Octopus Deploy supports this yet, but has anyone else figured a way to do this?

Details:
There can be zero to many script files (*.sql) in several subdirectories that need to be searched.
Each instance of a KEY needs to be replaced with the VALUE based on entries in the VARIABLE SET and the SCOPE (environment).
Example
  '~%SERVERNAME%~' gets replaced with 'ServerABC'

There can be many Key Value pairs.

  1. Support Staff 1 Posted by Robert Wagner on 14 Feb, 2018 06:20 AM

    Robert Wagner's Avatar

    Hi,

    Thank you for getting in touch. Unfortunately we don't have anything built in to do replacements inside archives. However, one of the options below may work:

    Option A:
    1) Deploy the package using a Deploy Package Command, which will extract the dacpac zip
    2) Run a script that does the replacements and re-creates the dacpac
    3) Deploy the DacPac

    Option B:
    1) Deploy the package using the transfer package command, which will put the dacpac onto the file system
    2) Run a script that looks inside the archive, extract the sql scripts one at a time, do any replacements and then put them back into the archive
    3) Deploy the dacpac

    For step 2 in those scenarios, you can use a PowerShell script (or C#/F# script). For powershell, you have a $OctopusParameters hash that contains all the variables configured in octopus scoped to that environment/step/etc (including library variables). Use this to easily see what it actually contains. You can loop over the keys in that hash, and check the sql file to see if a replacement needs to be made. If so, get the value from the hash and replace it in the sql.

    Hope that helps,

    Robert W

Reply to this discussion

Internal reply

Formatting help / Preview (switch to plain text) No formatting (switch to Markdown)

Attaching KB article:

»

Attached Files

You can attach files up to 10MB

If you don't have an account yet, we need to confirm you're human and not a machine trying to post spam.

Keyboard shortcuts

Generic

? Show this help
ESC Blurs the current field

Comment Form

r Focus the comment reply box
^ + ↩ Submit the comment

You can use Command ⌘ instead of Control ^ on Mac