creating an output variable azure devops from the Azure SQL InlineSqlTask task

Question

 YAML content :

- task: SqlAzureDacpacDeployment@1
  displayName: 'Azure SQL InlineSqlTask'
  inputs:
    azureSubscription: 'Service Connection'
    AuthenticationType: servicePrincipal
    ServerName: 'xxx.database.windows.net'
    DatabaseName: 'xxx-Dev'
    deployType: InlineSqlTask
    SqlInline: |
     select name as username,
                  create_date,
                  modify_date,
                  type_desc,
                  authentication_type_desc as authentication_type
                  from sys.database_principals where type_desc = 'EXTERNAL_USER'

I want to have the output of this t-SQL command stored in a variable. Can someone tell me how to do it?

Answer 1

Use a PowerShell script within Azure DevOps so you can directly get the variable and then use the set variable task to assign the output as an env: variable.

$query = "select name as username,

             create_date,

             modify_date,

             type_desc,

             authentication_type_desc as authentication_type

             from sys.database_principals where type_desc = 'EXTERNAL_USER'"


$clientid = "guid"

$tenantid = "guid"

$secret = "guid"

$request = Invoke-RestMethod -Method POST `

           -Uri "https://login.microsoftonline.com/$tenantid/oauth2/token"`

           -Body @{ resource="https://database.windows.net/"; grant_type="client_credentials"; client_id=$clientid; client_secret=$secret }`

           -ContentType "application/x-www-form-urlencoded"

$access_token = $request.access_token


$sqloutput = Invoke-Sqlcmd -ServerInstance $.database.windows.net -Database db$ -AccessToken $access_token -query $query


Write-Host "##vso[task.setvariable variable=GetVar;]$sqloutput"

I hope this helps.