Skip to content

FilterContributor does not remove ALTER TABLE WITH CHECK CHECK CONSTRAINT for Foreign Keys in the ignored schemas #60

@berezaol

Description

@berezaol

Expected behavior
All deployment steps related to ignored schemas and objects — including constraint changes — should be removed from the generated script.

Actual behavior
CREATE TABLE and FK steps are removed, but ALTER TABLE CHECK CHECK CONSTRAINT statement remain in the script.

Environment
SqlPackage Version: 170.3.93.6
AgileSqlClub.DeploymentFilterContributor Version: 1.6.2.netstandard2.1
SQL Server Version: 2022

Steps to reproduce
`CREATE TABLE [filter].[Table1] (
[Id] INT NOT NULL
,CONSTRAINT PK_Table1 PRIMARY KEY CLUSTERED (Id)
)
GO

CREATE TABLE [filter].[Table2] (
[Id] INT NOT NULL
,IdRef INT NOT NULL
,CONSTRAINT PK_Table2 PRIMARY KEY CLUSTERED (Id)
,CONSTRAINT FK_Table2_Table2 FOREIGN KEY (IdRef) REFERENCES [filter].[Table2] (Id)
)
GO
`
Power Shell script
$basePath = "you path with dacpac"

$sqlpackage = "$env:USERPROFILE.dotnet\tools\sqlpackage.exe"

& $sqlpackage /version

& $sqlpackage /Action:Publish
/SourceFile:"$basePath\SqlPackageFilterBugs.dacpac" /TargetConnectionString:"your connection string"
/p:AdditionalDeploymentContributors=AgileSqlClub.DeploymentFilterContributor /p:AdditionalDeploymentContributorArguments="SqlPackageFilter=IgnoreSchema(filter)"
/DeployReportPath:"$basePath\DeploymentReport.xml" /DeployScriptPath:"$basePath\DeploymentScript.sql"
/p:CreateNewDatabase='False'

Result
Initializing deployment (Start)
Starting AgileSqlClub.DeploymentFilterContributor
Starting AgileSqlClub.DeploymentFilterContributor
Command Line Filter: IgnoreSchema(filter)
adding ruleDefinition: Schema
Step removed from deployment by SqlPackageFilter, object: [filter], step type: Create
Step removed from deployment by SqlPackageFilter, object: [filter].[Table2], step type: Create
Step removed from deployment by SqlPackageFilter, object: [filter].[Table1], step type: Create
Step removed from deployment by SqlPackageFilter, object: [filter].[FK_Table2_Table2], step type: Create
Completed AgileSqlClub.DeploymentFilterContributor
Initializing deployment (Complete)
Analyzing deployment plan (Start)
Analyzing deployment plan (Complete)
Reporting and scripting deployment plan (Start)
Reporting and scripting deployment plan (Complete)
Updating database (Start)
Checking existing data against newly created constraints
An error occurred while the batch was being executed.
Updating database (Failed)
Could not deploy package.
Error SQL72014: Core Microsoft SqlClient Data Provider: Msg 4902, Level 16, State 1, Line 1 Cannot find the object "filter.Table2" because it does not exist or you do not have permissions.
Error SQL72045: Script execution error. The executed script:
ALTER TABLE [filter].[Table2] WITH CHECK CHECK CONSTRAINT [FK_Table2_Table2];

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions