Search code examples
sql-serversqlpackage

Can object creation in a database project (SqlPackager) be conditional?

I have a Microsoft SQL Server database project with hundreds of objects (tables, procedures, etc). The resulting database is used in two contexts though -- some customers get a database with all the objects, but some customers' databases contain only a subset of the objects. About 75% of them.

Is there a way to mark each object in the database project in such a way I can say whether it should be deployed or not based on a toggle of some sort?

I thought of a few options:

  1. Use pre/post deploy scripts. I really don't want to do this, as it defeats a lot of the benefits of using a database project in the first place.
  2. Install ALL objects, and have a post-deploy step that deletes the objects that we don't want in the "subset" install. Seem hackish to me.
  3. Break the one database project into two project: one with the required objects and one with the optional.
  4. Have two projects, one with "all" and one with the subset. 75% of the files will appear in both projects. Then I install the appropriate project's database

I think #3 and #4 sound doable, but I'm still hoping there is a simpler, more maintainable solution.

Solution

  • It looks like the proper way to do this is to create a second database project that references the first.

    Database A will have all the common objects, and Database B will have a reference to Database A (References -> Add database reference). I can ship database A as a "reduced" database, or database B as a "full" database.

    Still working on it, but it seems to be the cleanest and proper way of doing what I want.

    There is a good reference here from Microsoft, specifically creating a "composite project". https://msdn.microsoft.com/en-us/library/jj684584(v=vs.103).aspx