How to populate dbo.__MigrationoLog.release_version column via powershell?

PeterDaniels

From a previous post, I learned that we should be able to populate the release_version column of the dbo.__MigrationLog.  "If deploying via PowerShell, set the $ReleaseVersion variable to populate this column".  I am using Bamboo and have a deployment package that uses a powershell script to deploy a release using the Use-DatabaseReleaseArtifact powershell cmdlet.  I set the $ReleaseVersion variable as suggested, but still not seeing the column populated in the __MigrationLog table.

Monday

Actually I am just now going down the path of using the powershell cmdlets. looks like it is set in the New-DatabaseReleaseArtifact  -SqlCmdVariables switch. Example: 

$SqlCmdVariables = @{ReleaseVersion = $IterationNumber;}

$DBConnection = New-DatabaseConnection -ServerInstance $EAMDBServer -Database $PQDBName

$DBUpdate = New-DatabaseReleaseArtifact -Source $DBDeployFile -Target $DBConnection -SqlCmdVariables $SqlCmdVariables

Use-DatabaseReleaseArtifact $DBUpdate -DeployTo $DBConnection

Eddie D

Hi, thank you for your forum post.

Is there any warning message or log entry during deployment to indicate why this has not been set?

PeterDaniels

No, I'm not seeing a warning or error in the deployment log.  Here's the PS code for the inline PS Script task in my Bamboo deployment project:

<div>$ErrorActionPreference = "Stop"</div><div><br></div><div>#Will this set the release_version in the __MigrationLog table?</div><div>$ReleaseVersion = "${bamboo.deploy.release}"</div><div><br></div><div>$staging = New-DatabaseConnection -ServerInstance "localhost" -Database "SCAPOC_QA"&nbsp;</div><div><br></div><div># This is where the artifact was saved during build plan:</div><div>#C:\Users\peter\bamboo-home\artifacts\plan-753665\shared\build-00031\DB_Package_Artifact\SCAPOC_Pkg.1.31.nupkg</div><div>#</div><div># Where is it saved after the "Artifact Download" task?</div><div>#C:\Users\peter\bamboo-home\xml-data\build-dir\1998849-2129921\findme</div><div>#bamboo.agentWorkingDirectory	The path to the working directory on the agent. This is not the same as the Bamboo working directory.</div><div>#bamboo.build.working.directory	The path to the working directory for Bamboo. This is used by both the build plan and the deployment project.</div><div><br></div><div><br></div><div>#$path = Join-Path -Path ${bamboo.agentWorkingDirectory} -ChildPath "findme"</div><div>#Get-ChildItem : Cannot find path 'C:\Users\peter\bamboo-home\xml-data\build-dir\findme' because it does not exist.</div><div># we're missing the # -&nbsp;</div><div><br></div><div>$path = Join-Path -Path ${bamboo.build.working.directory} -ChildPath "findme"</div><div>$path</div><div>$file = Get-ChildItem -Path $path -Filter *.nupkg | Select-Object Name</div><div>$buildArtifact = $file.Name</div><div>$buildArtifact = Join-Path -Path $path -ChildPath $buildArtifact</div><div>"Here is our final path - OY VEY:"</div><div>$buildArtifact</div><div><br></div><div>#$buildArtifact = Join-Path -Path $buildArtifact -ChildPath "*.nupkg"</div><div><br></div><div>$releaseArtifact = New-DatabaseReleaseArtifact -Source $buildArtifact -Target $staging</div><div><br></div><div># Makes sure the directory we're about to create doesn't already exist.</div><div>#TODO: make this look like the UAT release path logic.</div><div>$exportPath = "C:\Export\DatabaseRelease\QA"</div><div>If (Test-Path $exportPath) {</div><div>&nbsp; &nbsp; rmdir $exportPath -Recurse -Force</div><div>}</div><div><br></div><div>$releaseArtifact | Export-DatabaseReleaseArtifact -Path $exportPath</div><div><br></div><div>Use-DatabaseReleaseArtifact $releaseArtifact -DeployTo $staging</div>

Monday

Not sure if this helps as I am not using Bamboo, just using a powershell script with the deployment file produced from the build at this time.

See this doco under: Deploying from PowerShell
https://documentation.red-gate.com/sca3/automating-database-changes/automated-deployment-with-sql-change-automation-core/sqlcmd-package-deployment

Just set the $ReleaseVersion variable, Example:

& { $DatabaseServer = $EAMDBServer; $ReleaseVersion = $IterationNumber; &$DBDeployFile }