SSIS执行SQL任务 - 验证MDS模型
问题描述:
我正在开发将数据从数据源抽取到MDS数据模型\实体的SSIS包。SSIS执行SQL任务 - 验证MDS模型
我有一个执行SQL任务,它应该验证MDS Facility实体。
下面是脚本:
DECLARE @ModelName nVarchar(50) = 'DEV ERP'
DECLARE @Model_id int
DECLARE @UserName nvarchar(50) = 'System'
DECLARE @User_ID int
DECLARE @Version_ID int
SET @User_ID = (SELECT ID
FROM mdm.tblUser u
WHERE u.UserName = @UserName)
SET @Model_ID = (SELECT Model_ID
FROM mdm.viw_SYSTEM_SCHEMA_VERSION
WHERE Model_Name = @ModelName)
SET @Version_ID = (SELECT MAX(ID)
FROM mdm.viw_SYSTEM_SCHEMA_VERSION
WHERE Model_ID = @Model_ID)
EXECUTE mdm.udpValidateModel @User_ID, @Model_ID, @Version_ID, 1
这是我得到的错误尝试执行任务时:
[Execute SQL Task] Error: Executing the query "DECLARE @ModelName nVarchar(50) = 'DEV ERP' DECLAR..." failed with the following error: "SYSERR515|Cannot insert the value NULL into column 'EnterUserID', table 'MDS.mdm.tblEvent'; column does not allow nulls. INSERT fails., @ErrorNumber = 515, @ErrorProcedure = "udpSystemEventSave", line 60". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
我不知道我怎么能解决这个问题,因为我不能将该变量声明为NOT NULL
值。在传递该变量之前,我该如何检查记录是否有效记录?
答
IF @User_ID IS NULL
BEGIN
-- Handle it here.
END
;