how to use SqlAzureDacpacDeployment@1 task result in the next task

user_19020793 0
Viewed 195

i need to query database in an azure pipeline to find out when was the last login to an environment and if it is more than 2 weeks destroy the environment. to do that i used the below task. but i dont know how can i store the variable to use in the next task. can someone please help me?

  - task: SqlAzureDacpacDeployment@1
    inputs:
      azureSubscription: ' A Service Connection'
      AuthenticationType: 'servicePrincipal'
      ServerName: 'myserver.database.windows.net'
      DatabaseName: 'mydb'
      deployType: 'InlineSqlTask'
      SqlInline: |
        DECLARE @LastLoginDate AS NVARCHAR(50)
        SELECT @LastLoginDate = [LastLoginDate]
        FROM [dbo].[AspNetUsers]
        WHERE UserName <> 'system'
        PRINT @LAstLoginDate
      IpDetectionMethod: 'AutoDetect'
azure-pipelines azure-sql-database
2 Answers

SQLInlineTask meant for execution of SQL Script on database.

Setting Variables in Pipeline Tasks available with either Bash or PowerShell.

PowerShell Script Task

$query = "DECLARE @LastLoginDate AS NVARCHAR(50)
        SELECT @LastLoginDate = [LastLoginDate]
        FROM [dbo].[AspNetUsers]
        WHERE UserName <> 'system'
        PRINT @LAstLoginDate"

# If ARM Connection used with service connection, ignore getting access token
$clientid = "<client id>" # Store in Variable Groups
$tenantid = "<tenant id>" # Store in Variable Groups
$secret = "<client secret>" # Store in Variable Groups

$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

# If ARM connection used with service connection, ignore AccessToken Parameter
$sqlOutput = Invoke-Sqlcmd -ServerInstance $.database.windows.net -Database db$ -AccessToken $access_token -query $query


Write-Host "##vso[task.setvariable variable=<variable name>;]$sqlOutput"

Bash

echo "##vso[task.setvariable variable=<variable name>;isOutput=true]<variable value>"

Within same job and different tasks, access it using $(<variable name>)

In Different job, access it using $[ dependencies.<firstjob name>.outputs['mystep.<variable name>'] ]

References:

https://docs.microsoft.com/en-us/azure/devops/pipelines/process/set-variables-scripts?view=azure-devops&tabs=bash

https://medium.com/microsoftazure/how-to-pass-variables-in-azure-pipelines-yaml-tasks-5c81c5d31763

user_98710730 
  - task: PowerShell@2
    inputs:
      targetType: 'inline'
      script: |
        $LoginDate=(Sqlcmd myserver -U $(Username) -P $(Password) -d mydatabase -q "SET NOCOUNT ON; SELECT LastLoginDate=min(LastLoginDate) FROM mytable WHERE UserName<>'system';")
        $Last=$LoginDate[2].trimstart()
        Write-host $Last
        Write-host "##vso[task.setvariable variable=LastLoginDate]$Last"
user_190207930
Related