Azure DevOps SQL Dacpac release job - extracting build variables

1
votes

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?

1
azure-devopsazure-sql-databaseazure-pipelines-release-pipeline

1 Answers

1
votes

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.