CODEWITHSUNDEEP
azure-devopsazure-sql-databaseazure-pipelines-release-pipeline
blobbles
blobbles

Reputation: 272

Azure DevOps SQL Dacpac release job - extracting build variables

I have a CI/CD pipeline running that releases a database (Visual Studio 2017 + SSDT) in Azure DevOps pipelines. The process works fine, however I am trying to log some Build information into the database.

I created a logging table for logging build information and used this as a reference for the information I want logged. I have setup the variables in the Project Properties "SQLCMD Variables" section to match the ones I want (BuildID, Build.TriggeredBy.DefinitionName, Build.Reason, Build.SourceBranch, Build.SourceVersion, Build.SourceMessage).

Visual Studio Project SQLCMD Variables

I put into the "Additional SQLPackage.ext Arguments" for the Azure DevOps release the sqlcmd for extracting these parameters:

/v:BuildID=$(Build.BuildId) /v:BuildTriggeredBy=$(Build.TriggeredBy.DefinitionName) /v:BuildReason=$(Build.Reason) /v:BuildSourceBranch=$(Build.SourceBranch) /v:BuildSourceVersion=$(Build.SourceVersion) /v:BuildSourceMessage=$(Build.SourceVersionMessage)

I created a post deployment script which will log these values into a logging table. Theoretically every time a release is successful, the build information should be logged:

INSERT INTO dbo.ReleaseLog ([BuildID],[TriggeredBy],[Reason],[SourceBranch],[SourceVersion],[SourceVersionMessage]) 
VALUES ('$(BuildID)','$(BuildTriggeredBy)','$(BuildReason)','$(BuildSourceBranch)','$(BuildSourceVersion)','$(BuildSourceMessage)')

Some of these work fine, but others don't work at all. The ones that work are: BuildID, SourceBranch, SourceVersion

The ones that don't (and return "$(VariableName)") are: Build.TriggeredBy.DefinitionName, Build.Reason, Build.SourceVersion

Results in logging table

Anyone know why? I have checked all the variables and they appear to be OK. I believe all the variables I am including should be available at the end of the release. It seems strange that some of the variables would work when others do not. I have triple checked (copied and pasted variable names between VS and release pipeline, plus copied the exact names from the MS site), so it doesn't seem like a code problem. Azure DevOps bug?

Upvotes: 2

Views: 1947

Answers (2)

David Yates
David Yates

Reputation: 2210

For anyone else that finds this, hopefully this will fill in a few more blanks.

Define a variable in the database project

First you have to define a variable in the database project file's properties (here I called mine YatesEnv): Database project SQLCMD variables tab

  1. Right click on the project and view its properties
  2. Select SQLCMD Variables
  3. Add a new variable with a default

Update a script file

Next update your Post or Pre deployment script file to use it and note how it comes through as a string, but I put it in a variable to help me when testing the script:

declare @Environment varchar(200) = '$(YatesEnv)'

if @Environment = 'DEV' 
begin
    INSERT INTO [dbo].[Person] ([FirstName],[LastName],[DateOfBirth]) VALUES ('Bob', 'DEV', DATEADD(YEAR, -30, GETDATE()))
end
else if @Environment = 'QA' 
begin
    INSERT INTO [dbo].[Person] ([FirstName],[LastName],[DateOfBirth]) VALUES ('Bob', 'QA', DATEADD(YEAR, -30, GETDATE()))
end

Update the release pipeline task

We want all this to be built once and then in each part of your RELEASE pipeline, you would populate the variable like this: Release pipeline SQL Server Database deploy task

  1. Select the "SQL Server Database deploy" task
  2. Update the "Additional Arguments" section using the /v:VariableName="some value" syntax. Note that I used double quotes for the variable contents because single quotes will result in a pipeline error.

Upvotes: 2

Leo Liu
Leo Liu

Reputation: 76760

Azure DevOps SQL Dacpac release job - extracting build variables

That because you created a post deployment script in the release pipeline, which could not get all those predefined variables in the build pipeline.

You could check those variables in the Classic release and artifacts variables. The variables Build.TriggeredBy.DefinitionName, Build.Reason, Build.SourceVersionMessage are not in the Classic release and artifacts variables, it in the Use predefined variables. That the reason why you could not get those specify variables in your release pipeline.

(You said Build.SourceVersion in your question, but according to the image you provided, it seems you wrote the wrong variable, it should be Build.SourceVersionMessage).

To resolve this issue, you could try to create post deployment script in the build pipeline.

Upvotes: 1

Related Questions