1

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
  1. 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];
  1. I cannot alter 3rd party schema due to company restrictions
  2. There are many lines of unnamed constraint and WITH CHECK CHECK constraints generated in the script.

Question:

  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

Any suggestions will be greatly helpful.

EDIT:

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
1
  • I tried various combination of parameters with no luck. /p:ExcludeObjectType=Defaults or /p:DropObjectsNotInSource=False /p:DoNotDropObjectType=Defaults etc – Santhoshkumar KB Jun 8 '20 at 7:17
0

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"
4
  • Thank you for the reply. The schema to be kept depends on the schema to be removed. Doesn’t dacpac deployment fail when the dependent objects(which are removed from dacpac) are not found? – Santhoshkumar KB Jun 13 '20 at 13:24
  • Indeed, you need to remove only objects that are not referenced by other remaining ones. My use case is avoiding customizations... If you want to reference objects in schema that you want removed, this tool will not help you. – Petr V Jun 14 '20 at 19:05
  • You can experiment with just removing the unnamed constraints though, I believe they are a separate element. Unzip the dacpac, open model.xml and try to find a regex string that matches only the unnamed constraints you want removed (sorry, replying from mobile, cannot test right now) – Petr V Jun 14 '20 at 19:11
  • I will give that a try, thank you. – Santhoshkumar KB Jun 15 '20 at 7:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.