Execute all .sql files in a folder against DB-Azure Pipelines - CI/CD

Question

I have all sql files checked into a repo. I have naming convention which allows to know which .sql files are used by any other .sql files.

How to execute all .sql files matching '..\Functions\BASE_*.sql' against a database. I tried as below, but it does not support multiple files being matched.

# 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 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://docs.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

Answer 1

This template combines matching files into a single file in the staging directory, publish it for debugging the pipeline, then execute it against the SQL server.

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"

 Want to become Azure Developer, check out this Microsoft Azure Training.