通过Azure DevOps部署Dacpac时Synapse部署失败问题排查

• 现有带数据的表修改结构后，通过Azure DevOps部署Dacpac时，系统识别到结构变更但报错，提示默认约束不能使用表达式，仅可指定常量，报错与动态掩码的default()函数相关。
• 手动删除表后重新部署则可成功创建表。

报错详情 #

##[error]Error SQL72014: Framework Microsoft SqlClient Data Provider: Msg 104338, Level 16, State 1, Line 1 An expression cannot be used with a default constraint. Specify only constants for a default constraint.
##[error]Error SQL72045: Script execution error. The executed script:
##[error]CREATE TABLE [CIM_People].[tmp_ms_xx_employee_leave_request] (
##[error] [requested_for_employee_profile_id] VARCHAR (100) MASKED WITH (FUNCTION = 'default()') NOT NULL,
##[error] [leave_start_dt] DATE MASKED WITH (FUNCTION = 'default()') NOT NULL,
##[error] [leave_end_dt] DATE MASKED WITH (FUNCTION = 'default()') NOT NULL,
##[error] [successfactors_leave_account_type_cd] VARCHAR (255) MASKED WITH (FUNCTION = 'default()') NOT NULL,
##[error] [successfactors_leave_request_status_nm] VARCHAR (255) MASKED WITH (FUNCTION = 'default()') NOT NULL,
##[error] [leave_payroll_calculation_cd] VARCHAR (10) MASKED WITH (FUNCTION = 'default()') NOT NULL,
##[error] [leave_request_last_modified_dt_utc] DATETIME MASKED WITH (FUNCTION = 'default()') NOT NULL,
##[error] [leave_request_external_cd] VARCHAR (255) MASKED WITH (FUNCTION = 'default()') NOT NULL,
##[error] [requestor_employee_profile_id] VARCHAR
##[error]System.Management.Automation.RemoteException
##[error]The Azure SQL DACPAC task failed. SqlPackage.exe exited with code 1

实际表脚本 #

CREATE TABLE [CIM_People].[employee_leave_request]
(
    [requested_for_employee_profile_id] varchar (100) MASKED WITH (FUNCTION = 'default()')  NOT NULL,
    [leave_start_dt] date MASKED WITH (FUNCTION = 'default()')  NOT NULL,
    [leave_end_dt] date MASKED WITH (FUNCTION = 'default()')  NOT NULL,
    [successfactors_leave_account_type_cd] varchar (255) MASKED WITH (FUNCTION = 'default()')  NOT NULL,
    [successfactors_leave_request_status_nm] varchar (255) MASKED WITH (FUNCTION = 'default()')  NOT NULL,
    [leave_payroll_calculation_cd] varchar (10) MASKED WITH (FUNCTION = 'default()')  NOT NULL,
    [leave_request_last_modified_dt_utc] datetime MASKED WITH (FUNCTION = 'default()')  NOT NULL,
    [leave_request_external_cd] varchar (255) MASKED WITH (FUNCTION = 'default()')  NOT NULL,
    [requestor_employee_profile_id] varchar (100) MASKED WITH (FUNCTION = 'default()')  NULL,
    [workflow_request_id] varchar (255) MASKED WITH (FUNCTION = 'default()')  NULL,
    [leave_day_qty] decimal (18, 2) MASKED WITH (FUNCTION = 'default()')  NULL,
    [leave_hour_qty] decimal (10, 2) MASKED WITH (FUNCTION = 'default()')  NULL,
    [leave_comment_desc] varchar (2000) MASKED WITH (FUNCTION = 'default()')  NULL,
    [leave_request_create_dt_utc] datetime MASKED WITH (FUNCTION = 'default()')  NOT NULL,
    [leave_last_modified_by_employee_profile_id] varchar (100) MASKED WITH (FUNCTION = 'default()')  NULL,
    [source_deleted_ind] varchar (1) MASKED WITH (FUNCTION = 'default()')  NOT NULL,
    [InsertedLoadId] INT NOT NULL,
    [InsertedLoadDate] DATETIME NOT NULL,
    [UpdatedLoadId] INT NULL,
    [UpdatedLoadDate] DATETIME NULL,
    [Type1SCDHash] VARCHAR(256) NOT NULL,    
    [BusinessKeyHash] VARCHAR(256) NOT NULL
)
WITH
(
    DISTRIBUTION = HASH([BusinessKeyHash]),
    CLUSTERED COLUMNSTORE INDEX
)

问题原因分析 #

1. SqlPackage.exe变更脚本生成逻辑错误
   部署带数据的表结构变更时，SqlPackage.exe会采用临时表迁移数据的策略（创建tmp_ms_xx_开头的临时表），但生成临时表脚本时，错误地将动态掩码的default()函数识别为列默认约束的表达式，而非动态掩码配置。SQL Server要求默认约束必须使用常量，不能用表达式，因此触发报错。

2. 新建表与变更表的脚本解析差异
   手动删除表后重新部署时，SqlPackage.exe生成的是全新的CREATE TABLE脚本，此时系统能正确识别MASKED WITH (FUNCTION = 'default()')为动态掩码配置，而非默认约束，因此可正常执行。

3. default()关键字的语义混淆
   default()在SQL Server中有双重含义：既是动态掩码函数，也是默认约束的关键字。SqlPackage.exe处理表结构变更时，解析逻辑出现混淆，把掩码配置误判为默认约束定义。

内容的提问来源于stack exchange，提问作者Gayatri