Generate multiple DACPACs targeting multiple target platforms

Question

I have an SSDT sql server database project of which I want to deploy to both an on premise SQL Server 2016 and SQL Azure.

It appears I can only set the project to target one platform

I want to be able to build this project (using VSTS build agents) and get multiple DACPAC files, one for each targeted platform.

This question Visual Studio Database project target platform in publish profile may actually be the answer. However I have a feeling I may need the individual DACPACs for the deployment scenario.

score 2 · Accepted Answer · answered Apr 27 '20 at 17:41

Being that I'm using Visual Studio for the build, I can passed a properties argument to the command line

/p:DSP=Microsoft.Data.Tools.Schema.Sql.SqlAzureV12DatabaseSchemaProvider

which lets me control the Database Schema Provider at build time. Using this at a YAML azure pipeline looks like this:

        - task: VSBuild@1
          inputs:
            solution: '$(build.SourcesDirectory)\mydatabase\whoopie.sqlproj'
            vsVersion: 'latest'
            msbuildArgs: '/p:DSP=Microsoft.Data.Tools.Schema.Sql.SqlAzureV12DatabaseSchemaProvider'

score 0 · Answer 2 · answered Nov 22 '17 at 21:07

0

You don't need individual dacpacs or technically even individual publish profiles. You need to set the option on publish to allow incompatible platforms. That will enable you to publish the DB to any supported platform as long as you're not using features that won't work with that version of SQL Server.

answered Nov 22 '17 at 21:07

Peter Schott

4,521
21
30

so its a deployment setting vs a build time setting? if possible pls show where this is set? – BozoJoe Nov 26 '17 at 05:38
Easiest way to see it is to right-click the project and select "Publish". You'll see a bunch of options, including the target database/server. You can change that and even set different options per server/database if you want. Behind the scenes, you'll build a "dacpac" file that can be re-used for multiple scenarios - you can search your file system for it to see it. – Peter Schott Nov 28 '17 at 13:17

score 0 · Answer 3 · answered Dec 16 '17 at 02:13

0

If you right click the Project and click "Publish..." then open Advanced, there is an option to ignore database incompatibility.

answered Dec 16 '17 at 02:13

Casey O'Brien

407
2
6
15

score 0 · Answer 4 · answered Apr 27 '20 at 14:41

Two different approaches I have used for something similar. Both are somewhat tedious, but work:

During your build process, use a script to modify the target platform and assembly name, and then build the project for each target platform you want to support.
Or, create separate database projects for each target platform. However, instead of copying the files into each project, you can link to them from the "master" project. In Visual Studio, you would "Add Existing Item", but then in the add dialog, you choose "Add as Link". Now, you only have to maintain the file in one place, but it is referenced by several different db projects.

The most tedious part of #2 is that every time you add a new file to the "master" project, you have to remember to link to it in the others.

Generate multiple DACPACs targeting multiple target platforms

4 Answers4