CODEWITHSUNDEEP
sqlsql-servert-sqlssisssis-2012
KB Singh
KB Singh

Reputation: 11

SSIS package precedence Constraint not working for multiple expressions

I have a SSIS package in which we want to see if the DataLoadStatusID == 2 then go to the path that copies the file and deletes from the initial import folder. This part works as expected.

And if we load the same file again, due to duplicate values the DataLoadStatusID !=2, then go to the path where the file does not get copied or deleted. The file should remain in the initial import folder.** This is not working when I re-load the same file the DataLoadStatusID !=2 does not work.**

DataLoadStatusId==2

AND

DataLoadStatusID !=2

I tired changing the Result Set to Single Row

Result Set Single Row

Set the Result Set to DataLoadStatusID

Set the Result Set

Set the Parameter. One thing to note is that the DataLoadId is the only parameter setup in the stored procedure. But the DataLoadStatusId is setup as a variable in the SSIS package only. DataLoadStatusID is not setup as a parameter in the Stored Porcedure. But we have a update statement in the stored procedure.

-- If there were validation errors (not just warnings, mark the data load as failed and exit procedure

DECLARE @ErrorCount INT = 0
SELECT @ErrorCount = COUNT(*) from dbo.ClaimLoadValidation where DataLoadId = @DataLoadId and ValidationStatusId = 2
IF (@ErrorCount > 0)
BEGIN
    UPDATE dbo.DataLoad set DataLoadStatusId = 3 where DataLoadId = @DataLoadId 

    RETURN
END

-- If everything worked, mark record as successful

UPDATE dbo.DataLoad set DataLoadStatusId = 2 where DataLoadId = @DataLoadId

Parameters

Once I run the package it gives me below error:

"[Execute SQL Task] Error: Executing the query "execute uspLoadClaimHartford ?" failed with the following error: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done. Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly."

Upvotes: 0

Views: 461

Answers (2)

Bob Rogers
Bob Rogers

Reputation: 1

Have you tried using just Expression in the Evaluation operation combo box? Since you are choosing Logical OR for multiple constraints, it's likely the Constraint is a "Success".

Upvotes: 0

HSS
HSS

Reputation: 176

Are you sure the DataLoadStatus variable is != 2. Maybe put a breakpoint and watch on it to check it

Upvotes: 0

Related Questions