4

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.

0

2 Answers 2

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.

1
  • 1
    The OP says those constraints belong to the 3d party schema over which they don't have control, so your suggestion doesn't seem to apply here, though in general it is definitely a good idea.
    – mustaccio
    Jun 4, 2021 at 12:26
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"
3
  • 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? Jun 13, 2020 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, 2020 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, 2020 at 19:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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