how to create a release pipeline to deploy all .sql scripts in a folder recursively

Question

I am using SQL Server database deploy task to deploy sql scripts to an on-prem SQL Server database. But the downside I see to this is I could only do 1 script at a time. This would work fine only when I have a small finite number of files but if the number of files increase this option would not scale up. What other options are available in Azure DevOps.

I did try the option of using wildcard **\*.sql and get the below error.

YAML:

steps:
- task: SqlDacpacDeploymentOnMachineGroup@0
  displayName: 'Deploy SQL Query'
  inputs:
    TaskType: sqlQuery
    SqlFile: '$(System.DefaultWorkingDirectory)\myScript.sql'
    ServerName: 'localhost'
    DatabaseName: 'DB_Name'

Answer 1

Update:

I can reproduce the same issue when using the wildcard **\*.sql.

The cause of the issue is that the task only supports defining one sql file. If the wildcard can matches multiple files, it will show the error.

What other options are available in Azure DevOps.

To meet your requirement, you can use script to combine multiple sql files into one sql file. Then you can specify the one sql file in SqlDacpacDeploymentOnMachineGroup task.

Here is an example:

steps:
- task: PowerShell@2
  inputs:
    targetType: 'inline'
    script: |
      $NewFileName = "Base.sql"

      $outFile = "$NewFileName"
       
      cls
      if((Test-Path $outFile) -eq $true) {Remove-Item -Path $outFile -Force}
       
      $files = Get-ChildItem -Path .\*.sql  -Recurse | Sort-Object -Property Name
      echo $files
       
      New-Item -ItemType file -Path $outFile -Force | Out-Null
       
      foreach($file in $files)
      {
          echo $file
          Write-Host "Appending file $file..." -ForegroundColor Gray
          $content = Get-Content -Path $file.FullName
          Add-Content -Path $outFile "----------------------------------------------------------------------------------------------------------------------------------------------------------------"
          Add-Content -Path $outFile "--      $File"
          Add-Content -Path $outFile "----------------------------------------------------------------------------------------------------------------------------------------------------------------"
          Add-Content -Path $outFile $content
          Add-Content -Path $outFile "GO`n"
      }
       
      Write-Host "Completed file $outFile" -ForegroundColor DarkGreen

- task: SqlDacpacDeploymentOnMachineGroup@0
  inputs:
    TaskType: 'sqlQuery'
    SqlFile: '$(build.Sourcesdirectory)\Base.sql'
    ServerName: 'localhost'
    DatabaseName: 'DB_Name'

The PowerShell script will combine all sql files into the new Base.sql file.