Referring to other files in SQL Pre-deployment script using Devops Self-Hosted agent

Question

We are using DevOps YAML pipelines to build and deploy database changes to our production environment. The database is deployed through a DACPAC file. We have a pre and post deployment file configured in the database project. In the predeployment file we add explicit database schema changes which would otherwise trigger data loss errors when the DACPAC is deployed, such as dropping objects or renaming tables or columns. But because the DACPAC deployment evaluates the database schema without taking into account the predeployment scripts, we still get these data loss warnings if we don't explicitly execute the predeployment script first. For this I have added a SQL Script task before the DACPAC task in the deployment YAML file. The issue is that we prefer to use multiple predeployment scripts to separate certain tasks. Because a database project can only have 1 predeployment script, we use SQLCMD statements to call the other files from the main script. For some reason when I execute the pipeline it will correctly execute the predeployment script, but then starts searching for the other files in the wrong folder. I have referenced them with the standard :r .\<filename.sql> notation but this doesn't seem to work. Removing the .\ also doesn't help. Below is the content of the predeployment script:

PRINT N'Execute PreDeployKeysAndSystemUsers.sql'
:r .\PreDeployKeysAndSystemUsers.sql

PRINT N'Execute PreDeployUsersAndRoles.sql'
:r .\PreDeployUsersAndRoles.sql

PRINT N'Execute PreDeployDDLStatements.sql'
:r .\PreDeployDDLStatements.sql

And this is the YAML snippet that executes this script:

    - task: SqlAzureDacpacDeployment@1
      displayName: 2. Execute PreDeployment Script
      inputs:
        azureSubscription: '${{ parameters.ServiceConnectionPrefix}}${{ parameters.env }}'
        AuthenticationType: 'servicePrincipal'
        ServerName: '$(ServerName)'
        DatabaseName: '${{ parameters.SQLDatabaseName }}'
        deployType: 'SqlTask'
        SqlFile: '$(Pipeline.Workspace)/${{ parameters.SQLProjectName }}/${{ parameters.SQLProjectName }}/01_preDeployment/Script.PreDeployment_t10-asqldb-mdw.sql'

When this is executed we get the following error:

Note how the path of the predeployment script and the script with the name "PreDeployKeysAndSystemUsers.sql" is different despite the .\ reference in the script. It is probably important to note that we use a self-hosted agent to run the deployment from.

I'm at a loss how to configure this. Any help would be appreciated.

Answer 1

I can reproduce the same issue when using the SqlAzureDacpacDeployment task.

Check the debug log of the error task. I noticed that the SqlAzureDacpacDeployment task will execute the sql file via the PowerShell script: D:\a\_tasks\SqlAzureDacpacDeployment_ce85a08b-a538-4d2b-8589-1d37a9ab970f\1.234.0\DeploySqlAzure.ps1

The task itself actually executes the DeploySqlAzure.ps1 file.

Therefore, the actual execution path of the task is under the Task configuration folder rather than the source file of the checkout repo.

For more detailed info, you can check the Task source code: DeploySqlAzure.ps1

To solve this issue, you can use CopyFiles@2 task/manual copy the related sql files to the path before the SqlAzureDacpacDeployment task : C:\agents\mdwh_prd\_work\_tasks\SqlAzureDacpacDeployment_ce85a08b-a538-4d2b-8589-1d37a9ab970f\1.234.0\

For example:

- task: CopyFiles@2
  displayName: 'Copy Files to: C:\agents\mdwh_prd\_work\_tasks\SqlAzureDacpacDeployment_ce85a08b-a538-4d2b-8589-1d37a9ab970f\1.234.0\PreDeployUsersAndRoles.sql'
  inputs:
    SourceFolder: folderpath
    Contents: |
     PreDeployKeysAndSystemUsers.sql
     PreDeployUsersAndRoles.sql
     PreDeployDDLStatements.sql
    TargetFolder: 'C:\agents\mdwh_prd\_work\_tasks\SqlAzureDacpacDeployment_ce85a08b-a538-4d2b-8589-1d37a9ab970f\1.234.0\PreDeployUsersAndRoles.sql'