By Richie Lee

Hello!

Artefacts play a very important part of build and release pipelines. For builds they contribute to the “build once, deploy many” process as an artefact is published at the end of the build and then consumed by a release at each satage/environment deployment. So in the context of an SSDT project, you compile the sqlproj and grab the resulting dacpac as a build artefact. Then for each release to dev/preprod/prod etc, you use the same dacpac that generates the deployment. And one of the great things about SSDT is that you can generate a script that will show you the changes that have been applied. This is great for understanding what changes have/will be made ( I say will be made as it is possible to generate the script without the chagnes being made.)

But how do we access that script?

If you’re using PoshSSDTBuildDeploy or dbatools to Publish a dacapc, an object is returned when running the publish that includes the path where the script was generated. So it would be easy if you’re using a YAML pipeline to do this. but not so with the Classic editor as it is not supported. So at this point you have two choices:

  1. Move to YAML pipelines
  2. Find an alternative

option 1 is something that we’ll all have to do sooner or later, especially when Azure DevOps is shutdown and we end up having to use GitHub actions….probably. But for now let’s focus on option 2. You can log files to be downloadable along with task logs.

##vso[task.uploadfile]local file path

This inconspicuous piece of code will allow a file to be downloaded in the zip file that includes the relesae logs. I have for example a script here that will publish a dacpac in a classic release editor. At the end of it al it will add the .sql script generated to the log files.

Function Invoke-DatabaseDeployment{
[cmdletbinding()]
param (
    [parameter(Mandatory = $true)] $WorkingFolder,
    [parameter(Mandatory = $false)] $serverName,
    [parameter(Mandatory = $false)] [string] $sqlAdministratorLogin,
    [parameter(Mandatory = $false)] [String] $sqlAdministratorLoginPassword,
    [parameter(Mandatory = $false)] [String] $connectionString,
    [parameter(Mandatory = $true)] [string] $DatabaseName,
    [parameter(Mandatory = $true)] [string] $DacpacPath,
    [parameter(Mandatory = $true)] [string] $PublishProfile,
    [parameter(Mandatory = $true)] [string] $ModulePath
)

if ($PSBoundParameters.ContainsKey('connectionString') -eq $false) {
    if ($PSBoundParameters.ContainsKey('sqlAdministratorLogin') -eq $true) {
        Write-Host "Using SQL Login to deploy"
        [string] $connectionString = "Server=$($serverName);Initial Catalog=$($DatabaseName);Persist Security Info=False;User ID=$($sqlAdministratorLogin);Password=$($sqlAdministratorLoginPassword);MultipleActiveResultSets=False;TrustServerCertificate=True;Connection Timeout=30;"
    }
    else {
        Write-Host "Using Integrated Security to deploy"
        [string] $connectionString = "integrated security=True;data source=$($serverName);initial catalog=$($DatabaseName);TrustServerCertificate=True;Connection Timeout=30;"
    }
}

Import-Module $ModulePath -Force

$dacFxFolder = Install-MicrosoftSqlServerDacFxx64 -WorkingFolder $WorkingFolder 

$dacFX = Join-Path -Path $dacFxFolder -ChildPath "\Microsoft.SqlServer.Dac.dll"
$deployScripts = New-Item -ItemType Directory -Force -Path "$WorkingFolder\deployScripts"

$PublishParams = @{
    dacfxPath                = $dacFX
    dacpac                   = (Resolve-Path $DacpacPath)
    publishXml               = (Resolve-Path $PublishProfile)
    targetConnectionString   = $connectionString
    targetDatabaseName       = $databaseName
    scriptPath               = $deployScripts
    GenerateDeploymentReport = $true
    GenerateDeploymentScript = $true
}
if ($PSBoundParameters.ContainsKey('getSqlCmdVars') -eq $true) {
    $PublishParams.Add("GetSqlCmdVars", $true)
}
$out = Publish-DatabaseDeployment  @PublishParams
Return $out
}

$instanceName = "poshssdtbuilddeploy"
SqlLocalDB.exe create $instanceName 13.0 -s
SqlLocalDB.exe info $instanceName

$serverInstance = "(localdb)\$instanceName"
$str = "SERVER=$serverInstance;Integrated Security=True;Database=master"

$dacpac = "$(System.DefaultWorkingDirectory)/_PoshSSDTBuildDeploy-BuildTestProject/PoshSSDTBuildDeploy/testProject/Northwind/bin/Debug/Northwind.dacpac"
$pubFile = "$(System.DefaultWorkingDirectory)/_PoshSSDTBuildDeploy-BuildTestProject/PoshSSDTBuildDeploy/testProject/Northwind/bin/Debug/Northwind.publish.xml"

$poshSSDTBuildDeploy = "$(System.DefaultWorkingDirectory)/_PoshSSDTBuildDeploy-BuildTestProject/PoshSSDTBuildDeploy/PoshSSDTBuildDeploy"


$output = Invoke-DatabaseDeployment -WorkingFolder $PSScriptRoot -connectionString $str -DatabaseName "Northwind" -DacpacPath $dacpac -PublishProfile $pubFile -ModulePath $poshSSDTBuildDeploy

Write-Host "here is the output"

Write-Output $output

Write-Host "here is the database summary..."

$dbScriptPath = $output.DatabaseScriptPath

Write-Output $dbScriptPath

Write-Host "And here I am appending to release log - "

Write-Host "##vso[task.uploadfile]$dbScriptPath"

Here it is in my release

classic release

In the logs of the release pipeline in Azure DevOps you can see the outputted object has all sorts of information on it, including the database summary (at the bottom of the image)

release logs

And now the command Write-Host "##vso[task.uploadfile]$dbScriptPath" will upload to the logs for me to download -

log folder

if you want to go next level on this you could package up any disparate files into a zip and then upload the zip file, mimicking how the artefacts task works in YAML.