VSTS online automated build no agent could be found

Question

For a manual build on vsts online deploying to azure things work fine. But when I trigger from my repo, the same build, I get this:

"No agent could be found with the following capabilities: msbuild, visualstudio, sqlpackage"

If I go to my Build def->General Tab

I should be, it's a little different between pressing build on on the vsts online website and having it triggered with a git repo checkin — Dan Parker, Apr 21 '17 at 16:03
Yeah, seems like it works fine without the sqlpackage (I removed the database project) - when it's triggered. It did work 1 time, but most times it fails. I bet it's a bug with their new Hosted VS2017 servers. — Dan Parker, Apr 21 '17 at 16:15
Maybe I can add a capability in ../_admin/_AgentQueue? System capabilities Shows information about the capabilities provided by this host But I'd need a name and a value for that. Any suggestions? — Dan Parker, Apr 21 '17 at 16:25
So that almost worked: When I added sqlpackage | C:\Program Files (x86)\Microsoft SQL Server\130\DAC\bin\sqlpackage.exe I get the following error: Unable to find the location of Dac Framework (SqlPackage.exe) from registry on machine TASKAGENT9-0002 — Dan Parker, Apr 21 '17 at 16:45

score 0 · Answer 1 · edited May 23 '17 at 11:46

There isn’t the SqlPackage capability in Hosted VS2017 agent. I submit a user voice here: VSTS SQL Package deploy with Hosted VS2017 agent.

The workaround is that you can deploy sql package to azure through PowerShell.

param(
    [string]$publish_profile,
    [string]$path_to_snapshots,
    [string]$database
)
$psPath=$PSScriptRoot 
write-output $psPath
Add-Type -Path "$psPath\lib\Microsoft.SqlServer.Dac.dll"
$dacProfile = [Microsoft.SqlServer.Dac.DacProfile]::Load($publish_profile)
$dacService = new-object Microsoft.SqlServer.Dac.DacServices($dacProfile.TargetConnectionString)

$files = Get-ChildItem  "$path_to_snapshots\*.dacpac"
Write-Output $path_to_snapshots
Write-Output $files.Length
foreach ($file in $files) 
{
    $fileName = $file.Name 
    Try
    {
        Write-output $fileName
            $dp = [Microsoft.SqlServer.Dac.DacPackage]::Load($file.FullName)
            $dacService.Deploy($dp, $database, $true) 
           Start-Sleep -s 300
    }
    Catch
    {
        Write-Host "$fileName deployment has been failed"  -foregroundcolor "red"
         $Error | format-list -force
        Write-Host $Error[0].Exception.ParentContainsErrorRecordException;
        Break
    }
}

Related thread: Deploy Dacpac packages via power shell script to Azure SQL Server

score 0 · Answer 2 · answered Sep 23 '19 at 12:26

I added a copy of this Microsoft script to my pipeline which returns the path to SQLPackage.exe on the agent.

https://github.com/Microsoft/azure-pipelines-tasks/blob/master/Tasks/SqlAzureDacpacDeploymentV1/FindSqlPackagePath.ps1

At time of writing the returned path is:

C:\Program Files\Microsoft SQL Server\150\DAC\bin\SqlPackage.exe

If at any points the location changes in theory it should just be a case of updating it to the new location reported by the Script.

VSTS online automated build no agent could be found

2 Answers2