[BUG]: Referring to other files in SQL Pre-deployment script using Devops Self-Hosted agent

[Kvwielink]

New issue checklist

• I searched for existing GitHub issues
• I read pipeline troubleshooting guide
• I checked how to collect logs

Task name

SqlAzureDacpacDeployment

Task version

1.234.0

Issue Description

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.

Environment type (Please select at least one enviroment where you face this issue)

• Self-Hosted
• Microsoft Hosted
• VMSS Pool
• Container

Azure DevOps Server type

dev.azure.com (formerly visualstudio.com)

Azure DevOps Server Version (if applicable)

No response

Operation system

Microsoft Windows Server 2022 Datacenter

Relevant log output

2024-02-28T11:04:30.8065618Z ##[section]Starting: 2. Execute PreDeployment Script
2024-02-28T11:04:30.8247472Z ==============================================================================
2024-02-28T11:04:30.8247659Z Task         : Azure SQL Database deployment
2024-02-28T11:04:30.8247778Z Description  : Deploy an Azure SQL Database using DACPAC or run scripts using SQLCMD
2024-02-28T11:04:30.8247974Z Version      : 1.234.0
2024-02-28T11:04:30.8248052Z Author       : Microsoft Corporation
2024-02-28T11:04:30.8248157Z Help         : https://docs.microsoft.com/azure/devops/pipelines/tasks/deploy/sql-azure-dacpac-deployment
2024-02-28T11:04:30.8248330Z ==============================================================================
2024-02-28T11:04:32.7709112Z Added TLS 1.2 in session.
2024-02-28T11:04:35.5881827Z Temporary inline SQL file: C:\Windows\ServiceProfiles\NetworkService\AppData\Local\Temp\tmpD347.tmp
2024-02-28T11:04:35.5995864Z Invoke-Sqlcmd -AccessToken "**********" -ServerInstance "t10-sql-p-01.database.windows.net" -Database "t10-12345-mdw"  -Inputfile "C:\Windows\ServiceProfiles\NetworkService\AppData\Local\Temp\tmpD347.tmp" 
2024-02-28T11:04:46.2464154Z Sql file: C:\agents\mdwh_prd\_work\1\t10-12345-mdw\t10-12345-mdw\01_preDeployment\Script.PreDeployment_t10-12345-mdw.sql
2024-02-28T11:04:46.2493136Z Invoke-Sqlcmd -AccessToken "**********" -ServerInstance "t10-sql-p-01.database.windows.net" -Database "t10-12345-mdw"  -Inputfile "C:\agents\mdwh_prd\_work\1\t10-12345-mdw\t10-12345-mdw\01_preDeployment\Script.PreDeployment_t10-12345-mdw.sql" 
2024-02-28T11:04:46.4958921Z ##[error]Cannot find path 'C:\agents\mdwh_prd\_work\_tasks\SqlAzureDacpacDeployment_ce85a08b-a538-4d2b-8589-1d37a9ab970f\1.234.0\PreDeployKeysAndSystemUsers.sql' because it does not exist.Check out how to troubleshoot failures at https://aka.ms/sqlazuredeployreadme#troubleshooting-
2024-02-28T11:04:46.5373288Z ##[section]Finishing: 2. Execute PreDeployment Script

Full task logs with system.debug enabled

 Sql file: C:\agents\mdwh_prd\_work\1\t10-12345-mdw\t10-12345-mdw\01_preDeployment\Script.PreDeployment_t10-12345-mdw.sql
Invoke-Sqlcmd -AccessToken "**********" -ServerInstance "t10-sql-p-01.database.windows.net" -Database "t10-12345-mdw"  -Inputfile "C:\agents\mdwh_prd\_work\1\t10-12345-mdw\t10-12345-mdw\01_preDeployment\Script.PreDeployment_t10-12345-mdw.sql" 
##[debug]No Firewall Rule was added
##[debug]Caught exception from task script.
##[debug]Error record:
##[debug]Cannot find path 'C:\agents\mdwh_prd\_work\_tasks\SqlAzureDacpacDeployment_ce85a08b-a538-4d2b-8589-1d37a9ab970f\1.234.0\PreDeployKeysAndSystemUsers.sql' because it does not exist.Check out how to troubleshoot failures at https://aka.ms/sqlazuredeployreadme#troubleshooting-
##[debug]At C:\agents\mdwh_prd\_work\_tasks\SqlAzureDacpacDeployment_ce85a08b-a538-4d2b-8589-1d37a9ab970f\1.234.0\DeploySqlAzure.ps1:226 char:5
##[debug]+     throw $errorMessage
##[debug]+     ~~~~~~~~~~~~~~~~~~~
##[debug]    + CategoryInfo          : OperationStopped: (Cannot find pat...roubleshooting-:String) [], RuntimeException
##[debug]    + FullyQualifiedErrorId : Cannot find path 'C:\agents\mdwh_prd\_work\_tasks\SqlAzureDacpacDeployment_ce85a08b-a538    -4d2b-8589-1d37a9ab970f\1.234.0\PreDeployKeysAndSystemUsers.sql' because it does not exist.Check out how to troubl    eshoot failures at https://aka.ms/sqlazuredeployreadme#troubleshooting-
##[debug] 
##[debug]Script stack trace:
##[debug]at , C:\agents\mdwh_prd\_work\_tasks\SqlAzureDacpacDeployment_ce85a08b-a538-4d2b-8589-1d37a9ab970f\1.234.0\DeploySqlAzure.ps1: line 226
##[debug]at , : line 1
##[debug]at , : line 22
##[debug]at , : line 18
##[debug]at , : line 1
##[debug]Exception:
##[debug]System.Management.Automation.RuntimeException: Cannot find path 'C:\agents\mdwh_prd\_work\_tasks\SqlAzureDacpacDeployment_ce85a08b-a538-4d2b-8589-1d37a9ab970f\1.234.0\PreDeployKeysAndSystemUsers.sql' because it does not exist.Check out how to troubleshoot failures at https://aka.ms/sqlazuredeployreadme#troubleshooting-
##[error]Cannot find path 'C:\agents\mdwh_prd\_work\_tasks\SqlAzureDacpacDeployment_ce85a08b-a538-4d2b-8589-1d37a9ab970f\1.234.0\PreDeployKeysAndSystemUsers.sql' because it does not exist.Check out how to troubleshoot failures at https://aka.ms/sqlazuredeployreadme#troubleshooting-
##[debug]Processed: ##vso[task.logissue type=error]Cannot find path 'C:\agents\mdwh_prd\_work\_tasks\SqlAzureDacpacDeployment_ce85a08b-a538-4d2b-8589-1d37a9ab970f\1.234.0\PreDeployKeysAndSystemUsers.sql' because it does not exist.Check out how to troubleshoot failures at https://aka.ms/sqlazuredeployreadme#troubleshooting-
##[debug]Processed: ##vso[task.complete result=Failed]
Finishing: 2. Execute PreDeployment Script
  

Repro steps

- 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'

[darrencomeau]

Have also come across this problem, with one master .sql file referencing several additional files using SQLCMD :r syntax. Files are in the right locations in the release artifact and so it is a reasonable working assumption that calling the master should find the additional files which were alongside it.

I have tried a work around which is to change the reference in the master script to accept a path variable. Here's an extract from my response to the issue on stack overflow

While the script file gets copied to another location it can still access the previous script files from the original artifact.

So I tried successfully to pass the original path location as a variable to the master script file

This would have the following change to your original file;

PRINT N'Execute PreDeployKeysAndSystemUsers.sql'
:r $(ScriptFolder)\PreDeployKeysAndSystemUsers.sql

PRINT N'Execute PreDeployUsersAndRoles.sql'
:r $(ScriptFolder)\PreDeployUsersAndRoles.sql

PRINT N'Execute PreDeployDDLStatements.sql'
:r $(ScriptFolder)\PreDeployDDLStatements.sql

And for the SQL Server database deploy task I updated the Additional Arguments to include

-Variable @("ScriptFolder=$(System.DefaultWorkingDirectory)\_My_Artifact_Folder\My_Script_Path" , "other_variables...")

Now the master script can be executed from anywhere and will still find the additional scripts as long as they're on the same build agent.

[github-actions]

This issue is stale because it has been open for 180 days with no activity. Remove the stale label or comment on the issue otherwise this will be closed in 5 days

[Kvwielink]

Any chance of an update on this? It's still an issue for us.