Is there a way to create an array in powershell and loop through it in Azure DevOps YAML?

Question

I'm trying to create a pipeline for our database deployments against 140+ databases on 10 instances. Our IT team has mentioned that there are situations where the databases will be moved from one instance to another for load balancing. So I can't trust that the database will stay on the same instance for each release. We also can onboard a new client without our team hearing anything about it. This means I have to write a pipeline that can dynamically deploy to only the database on the given instance. Each instance will have an individual agent.

The workflow I plan on right now.

Build the DacPac from our Master Branch.
Add stage that is tied to a environment that requires one of the people on our team to approve the release.
Run a powershell script to get all databases on instance and return as list.
Loop through the given list and create a task for each database.

Right now I'm having issues with the part where I pass the list of databases as a variable back to the YAML pipeline from powershell. Ideally I could return a list and then use the YAML looping feature to create a SQL Server Deploy task with the given database name. Is it possible to return a list from Powershell and loop through them using the looping feature? An example of the YAML I'm using below minus stuff I don't want shared out there.


/*Pipeline Being Called*/
variables:
- name: BuildConfiguration
  value: release
- name: BuildPlatform
  value: any cpu
- name: system.debug
  value: true
- name: datamode 
  value: 0 
- name: APIMode
  value: 0
- name: Instance 
  value: "[ServerInstance]"
- name: var01
trigger: 
 - FixingDRDeployments

stages: 
- stage: Powershell 
  pool: [Agent Pool Name]
  jobs:
  - job: Powershell
    steps:
       - task: PowerShell@2
         inputs:
           targetType: 'inline'
           script: |
             $ServerInstance = "$(Instance)"
             
             
             $Databases = Get-DbaDatabase -SqlInstance $ServerInstance 
             $ReturnValues = @()
             
             $query = "
                Query to validate database is one we want to deploy against. 
             "
             
             
             foreach ( $db in $Databases) {
               $Results = Invoke-DbaQuery -SqlInstance $serverInstance -Database $db.name -Query $query
               if ($Results.Valid -eq 1) {
                 $ReturnValues += $db.Name
               }
             }
             Write-Host $ReturnValues
             



#stages:
- template: deploytemplates/BuildTemplate.yml
- template: template.yml
  parameters:
    param: ["TestDatabase1","TestDatabase2"]
**^ Instead of having a defined set of static databases, I'd like to be able to pass an array of databases from the powershell script above.**

/*deploytemplates/BUildTemplate.yml*/

stages:
- stage: Build 
  jobs:
    - job: build 
      pool:
       name: mn-vs2017
       demands:
        - msbuild
        - visualstudio
      steps:
       - task: VSBuild@1
         displayName: 'Build solution **\*.sln'
         inputs:
           solution: '**\*.sln'
           vsVersion: "15.0"
           msbuildArgs: '/p:OutDir=$(build.artifactstagingdirectory) /p:CmdLineInMemoryStorage=True'
           platform: '$(BuildPlatform)'
           configuration: '$(BuildConfiguration)'
           clean: true
           maximumCpuCount: true
           msbuildArchitecture: x64
       - task: PublishBuildArtifacts@1
         displayName: 'Publish Artifact: MasterDacPac'
         inputs:
          PathtoPublish: '$(build.artifactstagingdirectory)'
          ArtifactName: DacPac

/*template.yml*/
parameters: 
  param : []


stages:
- stage: ApprovalGate1
  displayName:  ApprovalGate1
  dependsOn: Build
  jobs: 
  - deployment: 
    displayName: ApprovalGate1
    pool: [Pool Name Here]
    environment: Databases
    strategy:
      runOnce:
        deploy: 
         steps:
          - task: DownloadSecureFile@1
            name: PublishProfile
            displayName: 'Download secure file'
            inputs:
              secureFile: Production.publish.xml
          - ${{ each p in parameters.param }}:
             - task: SqlDacpacDeploymentOnMachineGroup@0
               displayName: ${{ p }}
               inputs:
                 DacpacFile: '$(Pipeline.Workspace)\DacPac\DacPacName.dacpac'
                 ServerName: $(Instance)
                 DatabaseName: ${{ p }}
                 PublishProfile: '$(PublishProfile.secureFilePath)'
               continueOnError: true
               condition: succeededOrFailed()

score 2 · Answer 1 · answered Aug 28 '20 at 08:15

No, this is not possible. If you create a variable in powershell you will create variable of string type. It is even worse:

Yaml variables have always been string: string mappings.

We are preparing to release a feature in the near future to allow you to pass more complex structures. Stay tuned!

You may use arrays for parameters but this is only static and you know it already:

steps:
- template: template.yaml
  parameters:
    instances: 
    - test1
    - test2
    server: someServer

Is there a way to create an array in powershell and loop through it in Azure DevOps YAML?

1 Answers1