How do I populate the __MigrationLog columns: version, package_version, release_version?

PeterDaniels

I am attempting to create a coherent vision of the various concepts of "version" and "release" using SCA and Bamboo.  One area where I see possibilities is in the __MigrationLog table.  Here, we have version, package_version, and release_version columns.  How do we populate these columns with values in our pipeline/toolset?  And how do they relate to other concepts of version and release:
1) The semantic version or file folders and names in SCA projects (e.g. 1.0.0-Baseline and 1.1.0-Changes) 
2) In Bamboo, we have "release-1"..."release-i"..."release-n" and of course our Build #.
3) In the New-DatabaseBuildArtifact -PackageVersion parm
4) In DLM, we have "schema version"
How do we work with all of these attributes to get a coherent system?  I would like to have a release name that looks like:
<Year>_<ReleaseCodeName>
and have multiple "releases" in Bamboo to QA during the dev cycle.  Finally, a single release to "higher" environments.
TIA,
-Peter

Mike U

These columns are documented in the MSDescription extended properties for the columns.

package_version:
If you have enabled SQLCMD Packaging in your SQL Change Automation project, or if you are using Octopus Deploy, this will be the version number that your database package was stamped with at build-time.

release_version:
If you are using Octopus Deploy, you can use the value in this column to look-up which release was responsible for deploying this migration.

If deploying via PowerShell, set the $ReleaseVersion variable to populate this column.

If deploying via Visual Studio, this column will always be NULL.

version:
The semantic version that this migration was created under. In SQL Change Automation projects, a folder can be given a version number, e.g. 1.0.0, and one or more migration scripts can be stored within that folder to provide logical grouping of related database changes.

PeterDaniels

Excellent - and I love that you used extended properties for the metadata.

PeterDaniels

How do these columns relate to DLM Dashboards "schema version"?  And, is there a way to link into the Bamboo "Release Version"?  I realize that's not fully a RG question...

Tianjiao_Li

We've got some information from the dev team with regards to your second question. 

The DLM Dashboard schema version is read from the database build artifact. This should be set as part of a build task. Unfortunately DLM Dashboard integration is only support for SQL Source Control projects at the moment and does not work for SQL Change Automation projects.

We are not Bamboo expert but believe this could be set on on the build artifact as part of a build step if these are defined as variables in Bamboo. 

PeterDaniels

That's good info, although a little vague. How do I set the "schema version" on the build artifact so dlm dashboard picks it up? And which bamboo variable do I set?

Edit: I'm using octopus deploy now, but the release_version and package_version columns are not getting populated. What do I med to do within octopus deploy to populate them?

PeterDaniels

@Tianjiao_Li, wondering if thou might have any additional info regarding my last post's questions?