Azure Pipelines - CI/CD: How to execute all .sql files in a folder against DB

Question

I have all the SQL files checked into a repo on Azure devops. I have naming convention that allows me to know which .sql files are used by other .sql files (ex. file creates a view that is used by a stored procedure). I am wanting to force the use of the repo for tracking code changes, and would prefer not to use dacpac file. I want each function / view / stored procedure to have it's own file.

My question, how would I execute all .sql files matching '..\Functions\BASE_*.sql' against a database from an azure pipeline? I tried the below, but does not support multiple files being matched. Is there a better option that does? Do I need to script a loop and do it myself?

# pipeline

trigger:
- master

pool:
  vmImage: 'windows-latest'

steps:
- task: SqlDacpacDeploymentOnMachineGroup@0
  inputs:
    TaskType: 'sqlQuery'
    SqlFile: '$(System.DefaultWorkingDirectory)\Functions\BASE_*.sql'
    ServerName: '$(SQL_ServerName).database.windows.net'
    DatabaseName: '$(SQL_DatabaseName)'
    AuthScheme: 'sqlServerAuthentication'
    SqlUsername: '$(SQL_UserName)'
    SqlPassword: '$(SQL_Password)'

The error I am getting is:

Starting: SqlDacpacDeploymentOnMachineGroup
==============================================================================
Task         : SQL Server database deploy
Description  : Deploy a SQL Server database using DACPAC or SQL scripts
Version      : 0.3.23
Author       : Microsoft Corporation
Help         : https://learn.microsoft.com/azure/devops/pipelines/tasks/deploy/sql-dacpac-deployment-on-machine-group
==============================================================================
##[error]Found more than one file to deploy with search pattern d:\a\1\s\Functions\BASE_*.sql. There can be only one.
Finishing: SqlDacpacDeploymentOnMachineGroup

michael · Accepted Answer · 2020-06-09T15:28:21.447

After a day's research and trial, the best I could come up with was to keep files separate in the repo, but then combine multiple files together in the CI/CD pipeline before running it against the DB.

I created a template to combine matching files into a single file in the staging directory, publish it for debugging the pipeline, then execute it against the SQL server.

The template is:

# Template for executing all SQL files matching a string search

parameters:
- name: path #$path = "$(System.DefaultWorkingDirectory)\Functions"
  type: string
- name: match #$match = "BASE_*.sql"
  type: string
- name: outPath #$outPath = "$(System.DefaultWorkingDirectory)\Functions"
  type: string
- name: outName #$outName = "BASE.sql"
  type: string

steps:
- task: PowerShell@2
  inputs:
    targetType: 'inline'
    script: |
      echo Source Files:
      Get-ChildItem ${{parameters.path}} -include ${{parameters.match}} -rec 
  displayName: 'Files to process: ${{parameters.match}}'
- task: PowerShell@2
  inputs:
    targetType: 'inline'
    script: |
      echo Creating: ${{parameters.outPath}}\${{parameters.outName}}
      Get-ChildItem ${{parameters.path}} -include ${{parameters.match}} -rec | ForEach-Object {gc $_; ""} | out-file ${{parameters.outPath}}\${{parameters.outName}}
  displayName: 'Combine: ${{parameters.outName}}'
- task: PublishPipelineArtifact@1
  inputs:
   targetPath: '${{parameters.outPath}}\${{parameters.outName}}'
   artifact: '${{parameters.outName}}'
   publishLocation: 'pipeline'
  displayName: 'Publish: ${{parameters.outName}}'
- task: SqlDacpacDeploymentOnMachineGroup@0
  inputs:
    TaskType: 'sqlQuery'
    SqlFile: '${{parameters.outPath}}\${{parameters.outName}}'
    ServerName: '$(SQL_ServerName).database.windows.net'
    DatabaseName: '$(SQL_DatabaseName)'
    AuthScheme: 'sqlServerAuthentication'
    SqlUsername: '$(SQL_UserName)'
    SqlPassword: '$(SQL_Password)'
  displayName: 'Create or Alter: ${{parameters.outName}}'
- task: PowerShell@2
  inputs:
    targetType: 'inline'
    script: Remove-Item ${{parameters.path}}\${{parameters.match}} -Recurse
  displayName: 'Delete Files: ${{parameters.match}}'

The main pipeline then calls the template with the different search strings.

trigger:
- master

pool:
  vmImage: 'windows-latest'

steps:
- task: PowerShell@2
  inputs:
    targetType: 'inline'
    script: MKDIR "$(System.DefaultWorkingDirectory)\\Combined\\Functions"
  displayName: 'Create Output Folder'
- template: azTemplate/CombineAndRunSQLFiles.yml # Functions: UTIL
  parameters:
    path: "$(System.DefaultWorkingDirectory)\\Functions"
    match: "UTIL_*.sql"
    outPath: "$(System.DefaultWorkingDirectory)\\Combined\\Functions"
    outName: "UTIL.sql"
- template: azTemplate/CombineAndRunSQLFiles.yml # Functions: BASE
  parameters:
    path: "$(System.DefaultWorkingDirectory)\\Functions"
    match: "BASE_*.sql"
    outPath: "$(System.DefaultWorkingDirectory)\\Combined\\Functions"
    outName: "BASE.sql"

Result:

Pool: Azure Pipelines
Image: windows-latest
Agent: Hosted Agent
Started: Today at 9:55 AM
Duration: 1m 6s

Job preparation parameters
5 artifacts produced
Job live console data:
Finishing: Job

Hi michael, would you minding accepting it as answer since you've added useful workaround here? Check [can I answer my self-question](https://stackoverflow.com/help/self-answer) and how to [accept own answer](https://stackoverflow.blog/2009/01/06/accept-your-own-answers/). — LoLance, Jul 02 '20 at 02:24

score 0 · Answer 2 · edited Jul 21 '20 at 23:05

How would I execute all .sql files matching '..\Functions\BASE_*.sql' against a database from an azure pipeline? I tried the below, but does not support multiple files being matched. Is there a better option that does? Do I need to script a loop and do it myself?

If you want to execute several sql files, you need to have several corresponding WinRM SQL Server DB Deployment tasks/steps. This task is by design to deploy one .dacpac or .sql file at a time, it doesn't support deploy multiple scripts.

According to I want each function / view / stored procedure to have it's own file. So if you have three scripts for function/view/stored procedure, then you need at least three SqlDacpacDeploymentOnMachineGroup@0 steps in your pipeline. Check similar issues here(#1,#2). There's no better option and you have to run this task several times for your different scripts.

Azure Pipelines - CI/CD: How to execute all .sql files in a folder against DB

2 Answers2