Multiple OR Conditions in SSIS Conditional Split

Question

I am working on a SSIS Task of loading data from the staging database to Live data base for a product workorder database.

I need to split the loading into New and Updated Values and based on the split, i will insert or update the records in the Live database.

I have created conditional split for New rows. However on creating condition for updated rows, my package is getting failed based on the reason

[Conditional Split [2]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "Conditional Split" failed because error code 0xC020902B occurred, and the error row disposition on "Conditional Split.Outputs[Updated Rows]" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.

I found out the error occurred because of the condition i have specified. I have too many AND and OR conditions in my conditional split.

In my requirement, i will have Workorder ID and ProductID as consistent, however there will be rows like Order Quantity, Stocked Qty, Scrapped Qty getting updated in each data load. So I created the condition in conditional split as below

(srcWorkOrderID == lkpWorkOrderID) && (srcProductID == lkpProductID) && (srcOrderQty != lkpOrderQty) || (srcStockedQty != lkpStockedQty) || (srcScrappedQty != lkpScrappedQty) ..... and so on.

So if my workorderid and product id is matching in my staging and live database and other columns are not matching, i need to update all the other non matching columns in the Live Database.

If I specify only one not equal to condition like (srcWorkOrderID == lkpWorkOrderID) && (srcProductID == lkpProductID) && (srcOrderQty != lkpOrderQty) my package executes perfectly.

However I need multiple not equal to comparisons in my condition.

Any workaround for this?????

Answer 1

De Morgan's laws

Negation - De Morgan's laws

The rules can be expressed in English as:

• The negation of "A and B" is the same as "not A or not B."
  • substitution form: (P∧Q)⟺¬(¬P∨¬Q)
• The negation of "A or B" is the same as "not A and not B."
  • substitution form: (P∨Q)⟺¬(¬P∧¬Q)

SSIS

Rows of the Conditional Split

• ∨ = "OR": must stand in one row. It does not mean a new row in the Conditional Split box!
• ∧ = "AND": must stand in one row unless there are only AND:s in the WHERE condition so that you can cast it to ∨ with (P∧Q)⟺¬(¬P∨¬Q). The latter (¬(¬P∨¬Q)) can be put in rows by each ∨ to mean the former (P∧Q).

Negation in SSIS

"Logical Not": "Negates a Boolean operand."

Best practice in programming as far as I have seen it: keep the conditions as they are. Do not turn around signs like ">" to "<=", instead write an exclamation mark in front: !(MY CONDITION).

It is easier and less error-prone if I take a condition from SQL, write it down in SSIS as it is, without negating anything, then embed it in brackets, and only as the last step negate it. You see that negating "OR" and "AND" needs to abide by the De Morgan's laws, and checking that again and again takes time and nerves.

"AND" blocks in an SSIS Conditional Split

The rows in a SSIS Conditional Split mean "OR" conditions. If you have just "AND" blocks like in (WHERE a AND b AND c AND d), you can:

• either put all of them in one row:

Case 1: a AND b AND c AND d

• or you can make four negated rows, like in (P∧Q)⟺¬(¬P∨¬Q):

1 . (¬P∨¬Q)

Case 1: !a
Case 2: !b
Case 3: !c
Case 4: !d

2. ¬(¬P∨¬Q)

The second negation is the "Conditional Split Default Output" arrow (and not any of the four cases arrows).

"OR" blocks in an SSIS Conditional Split must stay in one row

Now to your question. As soon as you have an "OR" like in (WHERE a AND b OR c AND d), you cannot spread the conditions over the rows of one Conditional Split anymore. Instead, "OR" blocks can only become "AND" blocks if you double negate them, but then they would need to be in one line or in many Conditional Split boxes, but they can never be split across the lines of one Conditional Split box. From the example:

• P = (a AND b)
• Q = (c AND d)

..so that from (P∨Q)⟺¬(¬P∧¬Q) we know that this can become a one-line "AND" condition if you double negate it: ¬(¬P∧¬Q)

This cannot be spread over the rows of a Conditional Split since each row stands for one "OR" block. If every row got one AND-condition that needs to be negated (first negation) and then only the rest that is not hit by this were the output (second negation), this would mean to ¬(¬P∨¬Q) which is not the aimed (P∨Q). Spreading "OR" blocks across many rows cannot be done in an SSIS Conditional Split box!

Here is a wrong example to show what you cannot do.

WHERE type = 'X' 
AND (birthday is null OR birthday < '1900-01-01' OR birthday > GETDATE())

Thus, this is wrong, "OR" blocks can never be split into the rows of a Conditional Split:

First "AND" block:

Second "AND" block (wrong):

Output arrow: Conditional Split Default Output

.. or shorter (and still wrong):

Output arrow: Conditional Split Default Output

You could also write one of the two with REPLACENULL(...,0) since only one of the two "OR" conditions needs to be hit by REPLACENULL(...,1), which happens as soon as birthday is NULL:

The output arrow is the "Conditional Split Default Output", thus, none of the two cases makes the output arrow. Again, doing it like that does not work since it does not mirror ¬(¬P∧¬Q), but instead ¬(¬P∨¬Q) although we want stay along with the SQL (P∨Q).

What can be done instead?

Three choices:

1. You can stay in one row of the Conditional Split box to get it done. This is bad to read if you have a long condition.

2. You can put each "OR" block in a Conditional Split box on its own and distinct union all of the data afterwards. This will be good to read but blows up the flow since you split the stream in two before you union them again and aggregate group by all rows. This looks clumpsy if you deal with a data flow that evaluates many Conditional Splits.

3. BEST CHOICE: Make a Derived Column and split your "OR" blocks into many temporary variables.

Derived Column 1: a AND b  
Derived Column 2: c AND d

Or the same in three rows:

The default names like "Derived Column 1" fit since everybody can read from this that they are temporary. And you do not need to care for the naming. Or choose another name, up to you.

Then, right after the Derived Column, put a Conditional Split and write in the first row:

Case 1: [Derived Column 1] || [Derived Column 2]

Output arrow: Case 1

This keeps the "OR" blocks in one row but makes it more readable.

And if you want to avoid any NULLs since that would crash the boolean data type, write:

Case 1: REPLACENULL([Derived Column 1] || [Derived Column 2],0)

Output arrow: Case 1

Answer 2

Found the solution. I had some columns with NULL values in my condition

We need to handle the null values in SSIS conditional split as SSIS will treat the entire condition as NULL if there are null values in the variables.

Due to this, my entire condition was returned NULL

As a workaround, I handled the null values by ISNULL function for each variable and my package got executed without any errors...

Answer 3

If you want to check the updations based on srcProductID and srcWorkOrderID.

Then add another braces seperatly for the all other columns(except above two)

(srcWorkOrderID == lkpWorkOrderID) && (srcProductID == lkpProductID) && 
(
(srcOrderQty != lkpOrderQty) || (srcStockedQty != lkpStockedQty) || (srcScrappedQty != lkpScrappedQty).....

)