Excluding a specific table from a SSDT deploy

Question

I have an existing database with everything in schema dbo. I have an SSDT project with objects I am adding to it with schema foo

I have a table that looks like this in the project:

CREATE table foo.a (
    id INT NOT NULL
        CONSTRAINT [PK_foo_a] PRIMARY KEY CLUSTERED
        CONSTRAINT [FK_foo_a] FOREIGN KEY REFERENCES [dbo].[a],
    desc NVARCHAR(50) NOT NULL
)

It depends on dbo.a. dbo.a has many columns that are foreign keys to other columns. Someone else (who maintains the default schema) might change dbo.a.

I'd like to simple store dbo.a as:

CREATE table dbo.a (
    id INT NOT NULL
        CONSTRAINT [PK_a] PRIMARY KEY CLUSTERED
)

So it gets built internally, but not deployed. Is that possible?

Answer 1

You can use the AgileSqlClub SqlPackage Deployment Filter.

Brief instructions reproduced from the original article by Ed Elliott:

1. Download the filter from agilesqlclub.codeplex.com
2. Put the DLL into the same folder as sqlpackage.exe

3. Add these command line parameters to your deployment:

   /p:AdditionalDeploymentContributors=AgileSqlClub.DeploymentFilterContributor /p:AdditionalDeploymentContributorArguments="SqlPackageFilter=IgnoreSchema(BLAH)"

   This will neither deploy, drop or alter anything in the BLAH schema.

See the original article for complete details.

Answer 2

Only managing part of a database with SSDT is difficult. If there were no dependencies then you could simply disallow it to drop objects and not include it in the project. Since there are dependencies, but you don't want to manage them, I think that you will have to trick the system.

One method comes to mind, I don't have the ability to test them right now and I have only used a "similar" method to this, not this one exactly.

Option #1:

1. Create a new database project with dbo.a in it.

2. Reference the table with 3-part naming. For the first part of the name, use a SQLCMD variable. E.g. [$(DatabaseName)].dbo.a.

3. Never deploy your foo database.

4. Via the publish files or cli interface, make $(DatabaseName) equal the same name as your real database.

Answer 3

A "clean" built-in way to do this (as of 2016) is using a saved Schema Compare file. You can:

1. Perform your Schema Compare (from your database project -> a target server) and uncheck any objects that you want to exclude for updating.
2. Save your Schema Compare to a *.scmp file
3. You can copy and paste the .scmp file to create versions for different environments if needed and simply edit in XML editor to change source (to point to build output dacpac file), connection, etc. or even manually add/modify items in the section.
4. Configure your dacpac deployment/publish command parameters to use the appropriate .scmp file for the publish. e.g. SqlPackage.exe /Action:publish /SourceFile:c:\project\schema_compare.scmp

More information here: https://devblogs.microsoft.com/ssdt/schema-compare-in-sqlpackage-and-the-data-tier-application-framework-dacfx/