Setting variable from a database query

andrew_w · 11 October 2021 02:37

We have a deployment where we need to do the following

Run SQL to insert some new reference data into the DB
Get the identifiers for the newly created records
Put the new identifier values into Octopus variables

Is the nice way to do this fully automated? Currently our SQL is run via DBUp.

Kenneth_Bates · 11 October 2021 03:11

Hi Andrew,

Thanks for getting in touch! My first thought is perhaps output variables would work for you here, so you can run your SQL query and store what’s returned as an output variable in your step. This should allow you to retrieve the data, store it in the variable and then call the variable all within your deployment process. Would that be a valid approach to addressing your requirement?

Another possibly relevant/similar idea would be to have an API script to add a project variable which you could add as a script in your process. The following script might be a good starting point.

github.com

OctopusDeploy/OctopusDeploy-Api/blob/master/REST/PowerShell/Variables/CreateEnvironmentScopedVariables.ps1

$baseUri = "http://output.url"           # the address of your octopus server

$apiKey = "API-APIKEYAPIKEYAPIKEY"           # an api-key from an account with permissions to create
                                             # variables on the environment you specify

$projectName = "Project Name" # If you don't know your project Id add your project name here
                              # and the script will print out your project Id on the first run,
                              # then you can add it below

$projectId = ""               # set this if you know it, the script will run faster if it has
                              # a projectId, otherwise it will look up the project using its name

$environment = "Production"   # the name of the environment you want to scope the variables to

$variablesToAdd = "First", "Second", "Third"  #all the variables you want to createe

function Get-OctopusResource([string]$uri) {
    Write-Host "[GET]: $uri"
    return Invoke-RestMethod -Method Get -Uri "$baseUri/$uri" -Headers $headers
}

This file has been truncated. show original

I hope that helps! Let me know what you think, or if I’m misinterpreting anything about what you’re after.

Best regards,

Kenny

system · 11 November 2021 03:11

This topic was automatically closed 31 days after the last reply. New replies are no longer allowed.