sql-servervisual-studiosql-server-data-toolsdacpacdac

Way to share source code for few SSDT projects?


Background: We have common part of database structure which is base for our product, this part is customised for customers. So we may have many final structures with common part.

My current solution split each of this to two SSDT projects: core project and customisation project(which have reference to core).

This approach generates two separate dacpacks, which is not desired because It lead to two step deploy: core dacpac is used to create database and then customisation dacpac is used to update it.

I would like to generate only one dacpac generated for both parts. Therefore I’m looking for way to share code between ssdt projects.

I’m know solution with sharing single files as a links, but that lead to a lot of manual work, especially with many final projects.

I tried shared projects (used by Xamarin) but they are limited to C# and VB projects.

I’m looking for any suggestions.


Solution

  • Further investigation lead me to solution that satisfy my needs:

    It is possible to configure publishing profile of SSDT deployment in a way that structures from reference projects are also deployed.

    Three things needs to be done:

    1. Check option "Include composite objects" in publishing advanced settings. (See Picture)
    2. Referenced dacpacs need to be in same folder during deployment as deployed one.
    3. Post/pre deployment scripts defined in referenced in project arent executed, but easy workaround exists: post/pre deployment script of final project may include scripts from referenced project by using ":r" directive.

    Important notice: it is not possible to deploy dacpack with that option via SSMS studio, it is possible via Visual Studio and sqlpackage.exe.

    enter image description here