Im looking to use Azure DevOps to deploy a database via dacpac file to an Azure SQL Managed Instance. We have multiple of the same databases all hosted on the same SQL MI, but for different environemnts. E.g. we could have two environments (Env1 & Env2), and two databases (DB1 & DB2) – in the same SQL MI, we then have the following Managed SQL DB’s; Env1_DB1, Env1_DB2, Env2_DB1 & Env2_DB2.

The issue is that inside one of the databases, there is a stored proc which references the other db. So when we deploy the database using the dacpac file, we need to use some sort of custom variable to pass into the pipeline, which would then be used to update all references to other db’s. E.g. following on from the above example, we could do with passing in a variable of either ‘Env1’ or ‘Env2’, which then gets used during (or post deployment) of the dacpac to update the stored proc reference to the correct environemnt.

We’re using SQLPackage to do the dacpac deployment in AZDO, and i can see from the documentation that you can pass in ‘/Variables’ to add custom variables – although im not sure how these could then get used in the dacpac deployment to change the db name prefix (or if thats even the right approach??) https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-publish?view=sql-server-ver16#parameters-for-the-publish-action