azure pipeline variable to hold return value of DBT core macro

I have created an Azure pipeline (CI) via YAML file that executes DBT core models/macros in given sequences.

I want to declare a variable that will hold the value returned by the DBT MACRO

I have so far tried the below :

 variables:
  dbtMacroOutput: '' 

- script: |
    echo $(Build.Reason)
    pip install --upgrade pip
    pip install dbt-snowflake --user
    dbt run-operation myDbtMacro
    $dbtMacroOutput := $(dbt run-operation myDbtMacro)  # Capture the actual output
    Write-Host "##vso[task.setvariable variable=dbtMacroResult]$dbtMacroOutput"
    echo "The dbt macro result is $(dbtMacroOutput)"
  displayName: Use dbtMacroOutput dbtMacroOutput: ''  # Initialize with an empty string

But when executing, I get the message

:= command not found

Can anyone suggest if it is possible to achieve?

Solution

There seem to be a few issues in your code:

The := syntax is not valid - use = operator to assign a value to a variable.
You should use a pwsh or PowerShell@2 task to run Powershell commands, instead of script
Variable dbtMacroOutput is being declared at the top but you're trying to set variable dbtMacroResult in the task?
You can't use the pipeline variable in the same step where it's being defined

Example

The below example shows how to set and use a pipeline variable in the same job.

To keep it simple, I removed the dbt related commands - it should be easy enough for you to adapt it to your scenario:

trigger: none

pool:
  vmImage: 'ubuntu-latest'

jobs:
  - job: A
    displayName: 'Set pipeline variable'
    steps:
      - pwsh: |
          # set other commands here
          $dbtMacroOutput="value1" # replace with output of dbt command

          Write-Host "##vso[task.setvariable variable=dbtMacroResult]$dbtMacroOutput"
        displayName: Set pipeline variable dbtMacroResult
        name: setVariableStep
      - pwsh: |
          Write-Host "The dbt macro result is $(dbtMacroResult)"
        displayName: Use pipeline variable dbtMacroResult

Please note that if you want to make a variable available to future jobs, you must mark it as an output variable by using isOutput=true.

See Set a multi-job output variable and Use output variables from tasks for more details.