Call system capability from self hosted agent on Azure DevOps

Question

Problem:

Inside my release pipeline, I want to use the sqlpackage.exe, which is installed on my self hosted agent.

---

Investigations:

• On the self hosted agent, SqlPackage.exe was automatically detected as System Capability: DevOps > SystemCapability > SQLPackage.exe
• It seems, that the SystemCapability has also been found from the Agent job, since sqlpackage == exists is defined as a demand here: Agent Job > Demand > sqlpackage == exists
• This way of calling the system capability "sqlpackage" didn't work: Failed function call in Pipeline

---

Any ideas how to call the SqlPackage from my hosted agent?

Answer 1

So you have few options:

• call full path C:\......\sqlpackage.exe (the simplest way)
• use Get-SqlPackagePath function to get path to SQL Package and then use this full path

function Get-SqlPackagePath {
<#
    .SYNOPSIS
    Find path to specific version of SqlPackage.exe
 
    .DESCRIPTION
    Finds the path to specific version of SqlPackage.exe
 
    Written by (c) Dr. John Tunnicliffe, 2019 https://github.com/DrJohnT/PublishDacPac
    This PowerShell script is released under the MIT license http://www.opensource.org/licenses/MIT
 
    .PARAMETER Version
    Defines the specific version of SqlPackage.exe to which you wish to obtain the path
        latest = use the latest version of SqlPackage.exe
        150 = SQL Server 2019
        140 = SQL Server 2017
        130 = SQL Server 2016
        120 = SQL Server 2014
        110 = SQL Server 2012
 
    .OUTPUTS
    The full path to the specific version of SqlPackage.exe you requested
 
    .EXAMPLE
    Get-SqlPackagePath -Version 130
 
    Return the full path to a specific version of SqlPackage.exe
 
    .EXAMPLE
    Get-SqlPackagePath -Version latest
 
    Return the full path to a latest version of SqlPackage.exe
 
    .NOTES
    This module requires SqlPackage.exe to be installed on the host machine.
    This can be done by installing Microsoft SQL Server Management Studio from https://learn.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-2017
 
#>
    [OutputType([string])]
    [CmdletBinding()]
    param(
        [Parameter(Mandatory = $true)]
        [ValidateSet('150', '140', '130', '120', '110', 'latest')]
        [string]$Version
    )

    $ExeName = "SqlPackage.exe";
    try {
        # always return x64 version if present
        $SqlPackageExes = Get-Childitem -Path "${env:ProgramFiles}\Microsoft SQL Server\$Version" -Recurse -Include $ExeName -ErrorAction SilentlyContinue;
        foreach ($SqlPackageExe in $SqlPackageExes) {
            $SqlPackageExePath = $SqlPackageExe.FullName;
            $ProductVersion = $SqlPackageExe.VersionInfo | Select-Object ProductVersion;
            break;
        }

        if (!($SqlPackageExePath)) {
            # try to find x86 version
            $SqlPackageExes = Get-Childitem -Path "${env:ProgramFiles(x86)}\Microsoft SQL Server\$Version" -Recurse -Include $ExeName -ErrorAction SilentlyContinue;

            foreach ($SqlPackageExe in $SqlPackageExes) {
                $SqlPackageExePath = $SqlPackageExe.FullName;
                $ProductVersion = $SqlPackageExe.VersionInfo | Select-Object ProductVersion;
                break;
            }
        }

        if (!($SqlPackageExePath)) {
            $VsPaths = Resolve-Path "${env:ProgramFiles(x86)}\Microsoft Visual Studio\*\*\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\$Version";
            foreach ($VsPath in $VsPaths) {
                $SqlPackageExes = Get-Childitem -Path $VsPath -Recurse -Include $ExeName -ErrorAction SilentlyContinue;
                foreach ($SqlPackageExe in $SqlPackageExes) {
                    $SqlPackageExePath = $SqlPackageExe.FullName;
                    $ProductVersion = $SqlPackageExe.VersionInfo | Select-Object ProductVersion;

                    break;
                    break;
                }
            }
        }

        if (!($SqlPackageExePath)) {
            $VsPaths = Resolve-Path "${env:ProgramFiles(x86)}\Microsoft Visual Studio*\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\$Version";

            foreach ($VsPath in $VsPaths) {
                $SqlPackageExes = Get-Childitem -Path $VsPath -Recurse -Include $ExeName -ErrorAction SilentlyContinue;
                foreach ($SqlPackageExe in $SqlPackageExes) {
                    $SqlPackageExePath = $SqlPackageExe.FullName;
                    $ProductVersion = $SqlPackageExe.VersionInfo | Select-Object ProductVersion;
                    break;
                    break;
                }
            }
        }

        Write-Verbose "SqlPackage $ProductVersion found here $SqlPackageExePath";
    }
    catch {
        Write-Error "Get-SqlPackagePath failed with error $Error";
    }
    return $SqlPackageExePath;
}

• call Agent REST API endpoint with enabled includeCapabilities and then parse response to get sqlpackage ful path and use full path