azure-sql-database - 如何在下一个任务中使用 SqlAzureDacpacDeployment@1 任务结果

Question

我需要在 Azure 管道中查询数据库，以查明上次登录环境的时间，如果超过 2 周则破坏环境。为此，我使用了以下任务。但我不知道如何存储要在下一个任务中使用的变量。有人可以帮帮我吗？

  - 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'

Answer 1

SQLInlineTask用于在数据库上执行 SQL 脚本。

在 Bash 可用的管道任务中设置变量或 PowerShell .

---

PowerShell 脚本任务

$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"

---

重击

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

在相同的作业和不同的任务中，使用 $(<variable name>) 访问它

在不同的工作中，使用 $[ dependencies.<firstjob name>.outputs['mystep.<variable name>'] ] 访问它

---

引用资料:

https://learn.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