Can I use the DeployReport option when deploying a dacpac through Release Management

Question

I'd like to be able to setup a WinRM dacpac deployment task in MS Release Management to create a report of the schema compare and not actually deploy the database. Then I could have an environment approval and abandon the deployment if unexpected changes are reported. If the changes are as expected, the next environment would actually deploy the database.

Is there a way to do that using the available WinRM DB deployment task? If so, how?

Answer 1

'Publish' is hardcoded in the task script, so we wound up creating a powershell script that does this. Mostly modified the relevant code from the task (https://github.com/Microsoft/vsts-tasks/tree/master/Tasks/SqlDacpacDeploymentOnMachineGroup) and imported the utility file used by the task (https://github.com/Microsoft/vsts-rm-extensions/blob/master/TaskModules/powershell/TaskModuleSqlUtility/SqlPackageOnTargetMachines.ps1). Then just changed the hardcoded value and added the output file parameter. Then we read in the report file and display it in the release log for the powershell task. abest's comment is a good idea, but it looks like we don't currently have that task available at our site.

param (
    [string]$dacpacFile = $(throw "dacpacFile is mandatory, please provide a value."), 
    [string]$publishProfile = $(throw "publishProfile is mandatory, please provide a value."),
    [string]$targetDBServer = $(throw "targetDBServer is mandatory, please provide a value."),
    [string]$targetDBName = $(throw "targetDBName is mandatory, please provide a value."),
    [string]$outputPath = $(throw "outputPath is mandatory, please provide a value.")
 )


 Import-Module "$PSScriptRoot\SqlPackageOnTargetMachines.ps1"


function Get-SqlPackageCmdArgsDeployReport
{
    param (
    [string]$dacpacPath,
    [string]$publishProfile,
    [string]$server,
    [string]$dbName
    )

    try
    {
        # validate dacpac file
        if ([System.IO.Path]::GetExtension($dacpacPath) -ne ".dacpac")
        {
            throw "Invalid Dacpac file [ $dacpacPath ] provided"
        }
    }
    catch [System.Exception]
    {
        Write-Verbose ("Could not verify DacPac : " + $_.Exception.Message) -Verbose
    }

    $sqlPkgCmdArgs = [string]::Format(' /SourceFile:"{0}" /Action:DeployReport', $dacpacPath)

    try
        {
            # validate output file
            if ([System.IO.Path]::GetExtension($outputPath) -ne ".xml")
            {
                throw "Invalid output file [ $outputPath ] provided, that should be an xml file really"
            }
            $sqlPkgCmdArgs = [string]::Format('{0} /OutputPath:"{1}"', $sqlPkgCmdArgs, $outputPath)
            }
        catch [System.Exception]
        {
            Write-Verbose ("Could not verify ouput path : " + $_.Exception.Message) -Verbose
        }

    if( ![string]::IsNullOrWhiteSpace($publishProfile) )
    {
         try
        {
            # validate publish profile
            if ([System.IO.Path]::GetExtension($publishProfile) -ne ".xml")
            {
                throw "Invalid Publish Profile [ $publishProfile ] provided"
            }
            $sqlPkgCmdArgs = [string]::Format('{0} /Profile:"{1}"', $sqlPkgCmdArgs, $publishProfile)
            }
        catch [System.Exception]
        {
            Write-Verbose ("Could not verify profile : " + $_.Exception.Message) -Verbose
        }

    }

    if( ![string]::IsNullOrWhiteSpace($dbName) )
    {
       $sqlPkgCmdArgs = [string]::Format('{0} /TargetServerName:"{1}" /TargetDatabaseName:"{2}"', $sqlPkgCmdArgs, $server, $dbName)
    }

   #Write-Verbose "Sqlpackage.exe arguments : $sqlPkgCmdArgs" -Verbose
    return $sqlPkgCmdArgs
}

function Format-XML ([xml]$xml, $indent=2) 
{ 
    $StringWriter = New-Object System.IO.StringWriter 
    $XmlWriter = New-Object System.XMl.XmlTextWriter $StringWriter 
    $xmlWriter.Formatting = “indented” 
    $xmlWriter.Indentation = $Indent 
    $xml.WriteContentTo($XmlWriter) 
    $XmlWriter.Flush() 
    $StringWriter.Flush() 
    Write-Output $StringWriter.ToString() 
}

$sqlPackage = Get-SqlPackageOnTargetMachine 

#Write-Verbose "So the path the SQL Package is $sqlPackage ?" -Verbose

$sqlPackageArguments = Get-SqlPackageCmdArgsDeployReport $dacPacFile $publishProfile $targetDBServer $targetDBName
Write-Verbose("Running ExecuteCommand -FileName ""$sqlPackage""  -Arguments $sqlPackageArguments") -Verbose

ExecuteCommand -FileName "$sqlPackage"  -Arguments $sqlPackageArguments 

[xml]$report = Get-Content $outputPath 

Format-XML $report -indent 4 | Write-Verbose -Verbose