Exclude certain schema along with unnamed constraints in SSDT

Question

Task

1. Automate database deployment (SSDT/dacpac deployment with CI/CD)
2. The database is a 3rd party database
3. It also includes our own customized tables/SP/Fn/Views in separate schemas
4. Should exclude 3rd party objects while deploying the database project(dacpac) to Production
5. Thanks to Ed Elliott for the AgileSqlClub.DeploymentFilterContributor. Used the dll to filter out the schema successfully.

Problem

1. The 3rd party schema objects(Tables) are defined with unnamed constraints(default / primary key) when creating the tables. Example:

   CREATE TABLE [3rdParty].[MainTable] 
   (ID INT IDENTITY(1,1) NOT NULL,
   CreateDate DATETIME DEFAULT(GETDATE()))  --There is no name given to default constraint
   

2. When I generate the script for deployment using sqlpackage.exe, I see following statements in the generated script.

   Generated the script using:

   "C:\Program Files\Microsoft SQL Server\150\DAC\bin\sqlpackage.exe" /action:script /sourcefile:C:\Users\User123\source\repos\DBProject\DBProject\bin\Debug\DBProject.dacpac /TargetConnectionString:"Data Source=MyServer; Initial Catalog=MSSQLDatabase; Trusted_Connection=True" /p:AdditionalDeploymentContributorPaths="C:\Program Files\Microsoft SQL Server\150\DAC\bin\AgileSqlClub.SqlPackageFilter.dll" /p:AdditionalDeploymentContributors=AgileSqlClub.DeploymentFilterContributor /p:AdditionalDeploymentContributorArguments="SqlPackageFilter=IgnoreSchema(3rdParty)" /outputpath:"c:\temp\script_AfterDLL.sql"

   Script Output:

   /*
   Deployment script for MyDatabase
   This code was generated by a tool.
   Changes to this file may cause incorrect behavior and will be lost if
   the code is regenerated.
   */
   ...
   ...
   GO
   PRINT N'Dropping unnamed constraint on [3rdParty].[MainTable]...';
   GO
   ALTER TABLE [3rdParty].[MainTable] DROP CONSTRAINT [DF__MainTabl__Crea__59463169];
   ...
   ...
   ...(towards the end of the script)
   ALTER TABLE [3rdParty].[MainTable_2] WITH CHECK CHECK CONSTRAINT [fk_518_t_44_t_9];
   

3. I cannot alter 3rd party schema due to company restrictions

4. There are many lines of unnamed constraint and WITH CHECK CHECK constraints generated in the script.

Questions

1. How can I be able to remove the lines to DROP unnamed Constraint on 3rd party schemas? - Even though the dll excludes 3rd party schema, it still has these unnamed constraints scripted/deployed. Also, it is not adding them back too.
2. How can I be able to skip/remove generating WITH CHECK CHECK CONSTRAINT on 3rd party schemas

Also, I found another issue. The deployment will not succeed due to:

Rows were detected. The schema update is terminating because data loss might occur

Output

/*
The column [3rdParty].[MainTable_1].[Col1] is being dropped, data loss could occur.
The column [3rdParty].[MainTable_1].[Col2] is being dropped, data loss could occur.
The column [3rdParty].[MainTable_1].[Col3] is being dropped, data loss could occur.
The column [3rdParty].[MainTable_1].[Col4] is being dropped, data loss could occur.
*/
IF EXISTS (select top 1 1 from [3rdParty].[MainTable_1])
    RAISERROR (N'Rows were detected. The schema update is terminating because data loss might occur.', 16, 127) WITH NOWAIT
GO

I tried various combination of parameters with no luck. /p:ExcludeObjectType=Defaults or /p:DropObjectsNotInSource=False /p:DoNotDropObjectType=Defaults etc.

Answer 1

SSDT dropping unnamed constraints is telling you that you didn't name the constraints when you created them. They get assigned a dynamic name by SQL during the deploy. Since the project does not contain the dynamic name SQLPackage sees a difference and wants to drop/create.

Name your constraints and this won't happen.

Answer 2

You can use utility DacpacTool to remove the schema completely from a dacpac file - I wrote it to do exactly this (among other things). It is quite rough on the edges so far, but it works.

• Extract dacpac from target database to MSSQLDatabase.dacpac
• Create a file blacklist.txt with one line: <References Name="\[SchemaThatYouWantRemoved]
• DacpacTool.exe filter -f MSSQLDatabase.dacpac -b blacklist.txt
• DacpacTool.exe filter -f DBProject.dacpac -b blacklist.txt
• Script the diff between the two dacpacs. DeploymentFilterContributor is no longer needed: sqlpackage.exe /action:script /sourcefile:DBProject.dacpac /TargetFile:"MSSQLDatabase.dacpac" /outputpath:"c:\temp\script_AfterDLL.sql"