How to specify that .dacpac should use default SQLCMD variable values specified in VS2022 database project?

Question

I have an Azure SQL database project which uses SQLCMD variables to set values dynamically. I am able to reference these variables as part of publish profiles that accompany the project and can even override them.

I am using Visual Studio 2022 RC.

I also have an Azure DevOps pipeline which checks out projects builds .dacpac and moves the .dacpac into the requisite directories prior to the deploy task that deploys the database into Azure.

The deploy task SqlAzureDacpacDeployment@1 is failing. The preceding build task is producing a .dacpac without the default SQLCMD variable values which are set at the project level.

##[error]*** An error occurred during deployment plan generation. Deployment cannot continue.

##[error]Missing values for the following SqlCmd variables:var1 var2.

##[error]The Azure SQL DACPAC task failed. SqlPackage.exe exited with code 1.Check out how to troubleshoot failures at https://aka.ms/sqlazuredeployreadme#troubleshooting-

To give some additional context, this is the part of the CI pipeline that is building and deploying.

- task: VSBuild@1
  displayName: 'Build projA database.'
  inputs:
    solution: 'projA\\projA.sqlproj'
    vsVersion: 16.0
    platform: 'x64'

- task: SqlAzureDacpacDeployment@1
  displayName: 'Deploy projA database.'
  inputs:
    azureSubscription: '$(ServiceConnectionName)'
    ServerName: '$(ServerName)'
    DatabaseName: '$(DatabaseName)'
    SqlUsername: '$(ServerUserName)'
    SqlPassword: '$(ServerUserPassword)'
    DacpacFile: '$(Build.SourcesDirectory)\\projA\\bin\\Output\\projA.dacpac'

I thought that the default values would be used when creating the .dacpac. This is not the case.

I tried also setting the local values to the same as default (these were previously blank) but the same error arose.

I have had a look a this article that indicates that additional variables can be supplied when deploying. I am not sure if I could apply values to existing variables using the AdditionalArguments clause in the deploy task.

Ideally I want the .dacpac to be produced with the SQLCMD default values supplied if not overridden, surely this should be the default behaviour. I wouldn't want to maintain values in multiple locations, this should be captured in the project.

I have read How to use multiple SQLCMD variables with SqlPackage.exe while deploying a DacPac? but this has not answered my question.

Answer 1

To solve this I made use of the publishProfile argument (last line in the code) and referenced an existing publish profile which applied the default SQLCMD variable values to the target .dacpac during deployment via sqlPackage.exe.

- task: SqlAzureDacpacDeployment@1
  displayName: 'Deploy projA database.'
  inputs:
    azureSubscription: '$(ServiceConnectionName)'
    ServerName: '$(ServerName)'
    DatabaseName: '$(DatabaseName)'
    SqlUsername: '$(ServerUserName)'
    SqlPassword: '$(ServerUserPassword)'
    DacpacFile: '$(Build.SourcesDirectory)\\projA\\bin\\Output\\projA.dacpac'
    publishProfile: '$(Build.SourcesDirectory)\\projA\\projA.publish.xml'

I also had to switch from using Azure Active Directory Interactive authentication to SQL authentication because it isn't supported (because this is automation). I was using AADI Authentication in my publish profile.

I will use Azure Active Directory Integrated when moving this to a corporate VM which I authenticate to using that AAD account.

Another issue I faced was that the Azure SQL Database Instance blocks all IPs that are not on the green list, and I do not want to grant blanket access. I was using Microsoft Hosted agent which does not have a static IP. I installed an agent on my local machine, my public IP is green listed and so the deployment succeeds.