/p:ScriptDeployStateCheck=True causes deploy script to ignore errors.

[asrichesson]

• SqlPackage or DacFx Version: 162.0.52.1
• .NET Framework (Windows-only) or .NET Core: .NET Core
• Environment (local platform and source/target platforms): Microsoft SQL Server 2019 (RTM-CU12) (KB5004524) - 15.0.4153.1 (X64) Jul 19 2021 15:37:34 Copyright (C) 2019 Microsoft Corporation Express Edition (64-bit) on Windows 10 Home 10.0 (Build 19045: )

Steps to Reproduce:

1.Use any existing ssdt project and deploy it
2. Make a change in the project and build it to a dacpac
3. Script the changes using sqlpackage /a:Script etc. using /p:ScriptDeployStateChecks=True
4. :on error ignore is added after the state checks in the script and before the deployment scripts begin.
5. Expected: :on error ignore is not added to the script

Why does this matter?
When :on error ignore is active, the script will continue executing if it hits an error.
Scripting additional state checks should not affect error handling during deployment. More seriously, certain scripts contain state checks that are designed to halt the deployment script if it detects a possible data loss scenario. Take this script that it generated for me with /p:ScriptDeployStateChecks=True:

---removed state checks for brevity
:on error ignore
GO
USE [$(DatabaseName)];

GO
/*
The column [dbo].[MyTable].[Value] is being dropped, data loss could occur.
*/

IF EXISTS (select top 1 1 from [dbo].[MyTable])
    RAISERROR (N'Rows were detected. The schema update is terminating because data loss might occur.', 16, 127) WITH NOWAIT
GO
PRINT N'Altering SqlTable [dbo].[MyTable]...';

GO
ALTER TABLE [dbo].[MyTable] DROP COLUMN [Value];

GO
PRINT N'Update complete.';

When i run this script in azure data studio using sqlcmd on, the script does not stop when it raises an error but continues to dop the column! This seems like a serious bug.

However, if I script with /p:ScriptDeployStateChecks=False, the script does not contain :on error ignore and properly stops when it hits an error.

I've attached two scripts one with State checks on and one with state checks off.
StateChecksOFF.sql.txt
StateChecksON.sql.txt

Did this occur in prior versions? If not - which version(s) did it work in?

(DacFx/SqlPackage/SSMS/Azure Data Studio)