SSIS Execute SQL Task - Validating MDS model

Question

I'm developing an SSIS package that will pump data from a Data Source to an MDS data model\entity.

I have an execute SQL task which should validate the MDS Facility entity.

Here is the script:

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

Here is the error I'm getting when trying to execute the task:

[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.

I was wondering how can I overcome this issue since I cannot declare the variable as a NOT NULL value. Before passing that variable how can I check the record is valid record or not?

Chris Mack · Accepted Answer · 2017-10-09 20:57:25Z

0

IF @User_ID IS NULL
    BEGIN

        -- Handle it here.

    END
;

answered Oct 9, 2017 at 20:57

Chris Mack

5,2082 gold badges16 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SSIS Execute SQL Task - Validating MDS model

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related