Sqlproj SDK-style set sqlcmd variable at build innorder to add version number in version table

Question

I am using SDK-style SQL project in Visual Studio Code to handle my database versioning and generate dacpac file.

I am using azure pipelines to build and release my database.

I have tried many different ways, but i am not able to set variable at build time. My goal is to set automatically a version, maybe generated by git version or sometbing similar at build time to insert this value in a version table with a description I want to get from my pull-request description.

I have tried to use sqlcmd variables but it is not set at build. I have also tried to use variable DacVersion. The dacpac version number is correctly set but if I want to use $(DacVersion) in my insert post deployment script I have a build error saying this variable is not defined. If I defined it in sqlproj file, it is not working.

Any idea to help me acheive this please ?

Answer 1

If I defined it in sqlproj file, it is not working.

This looks like a correct approach. Use sqlpackage CLI arguments to assign version number during deploy phase like

/v:"DacVersion=1.0.123"

docs similar question