SSIS Conditional Split Error - The expression evaluated to NULL, but the "Conditional Split" requires a Boolean results

Question

It is my first time using this software. I am trying to split the value but it is showing this and I do not have any null have when I see my source but when I preview it, it shows null value in the first 200 columns but I have only 15 columns.

Error: 0xC020902B at Data Flow Task, Conditional Split [2]: The expression "[Copy of RATE_INR] > 1000" on "Conditional Split.Outputs[Case 1]" evaluated to NULL, but the "Conditional Split" requires a Boolean results. Modify the error row disposition on the output to treat this result as False (Ignore Failure) or to redirect this row to the error output (Redirect Row). The expression results must be Boolean for a Conditional Split. A NULL expression result is an error.

Answer 1

I guess it is not always good to fix only the conditional split since you might lose some rows only since there was a NULL in some field upstream. It might be that the [Copy of RATE_INR] should not reach the conditional split as a NULL, and you need to check that.

In my data flow, I had the same error at MyColumnForUnpivoting == TRUE of the conditional split:

[MyColumnForUnpivoting008 [871]] Error: The expression "MyColumnForUnpivoting == TRUE " on "MyColumnForUnpivoting008.Outputs[Fehler]" evaluated to NULL, but the "MyColumnForUnpivoting008" requires a Boolean results. Modify the error row disposition on the output to treat this result as False (Ignore Failure) or to redirect this row to the error output (Redirect Row). The expression results must be Boolean for a Conditional Split. A NULL expression result is an error.

I changed it at first to:

ISNULL(MyColumnForUnpivoting) ?  FALSE  : MyColumnForUnpivoting ==  TRUE 

I then found this Q/A and changed the conditional split to:

REPLACENULL(MyColumnForUnpivoting, FALSE ) ==  TRUE 

which shortens it a bit with the same outcome. Yet, I changed my mind and checked where the NULL of that "MyColumn" came from:

The Derived Column had the code:

FINDSTRING(Vorname,"#",1) > 0 || FINDSTRING(Nachname,"#",1) > 0 || FINDSTRING(Geburtsname,"#",1) > 0 || FINDSTRING(Geburtsort,"#",1) > 0 || FINDSTRING(Strasse,"#",1) > 0 || FINDSTRING(Adresszusatz,"#",1) > 0 || FINDSTRING(Ort,"#",1) > 0

What happens here is that once that just one of the "OR"-linked column checks returns NULL (that is, if one of the fields is NULL), the whole expression becomes NULL. That is not the aim here, this code checks whether there is a "#" somewhere in the address fields, and a NULL in just one field would dominate any findings.

I changed the expression to:

FINDSTRING(REPLACENULL(Vorname,""),"#",1) > 0 || FINDSTRING(REPLACENULL(Nachname,""),"#",1) > 0 || FINDSTRING(REPLACENULL(Geburtsname,""),"#",1) > 0 || FINDSTRING(REPLACENULL(Geburtsort,""),"#",1) > 0 || FINDSTRING(REPLACENULL(Strasse,""),"#",1) > 0 || FINDSTRING(REPLACENULL(Adresszusatz,""),"#",1) > 0 || FINDSTRING(REPLACENULL(Ort,""),"#",1) > 0

And changed the conditional split back to MyColumnForUnpivoting == TRUE and the data flow ran through without losing the needed "#"-rows.

I then checked all of the other conditional splits and found that in some code, I wanted the NULL to lead to a FALSE in the conditional split, for example when a number was wrong or 0, but not if it was NULL, since then, it was not set at all. Thus, it is not always needed to replace the NULL:s, instead check each code for its aim.

ISNULL(MyColumn) ? TRUE in the Derived Column is better than fixing the NULL in the conditional split

Still, it is better to keep the conditional split clean from NULL replacements and do this instead in the step before.

Here is how you would pass a NULL in the row of the column "number" as FALSE (so that it is not marked). In my workflow, a TRUE means that there is something wrong and should be marked for the unpivoted column while the rest of the rows can be filtered out with a FALSE unless they match one of the other two conditions:

ISNULL(number) ?  FALSE  : LEN(TRIM(number)) == 0 || LEN(TRIM(number)) != 1

Which is the same as:

REPLACENULL(LEN(TRIM(number)) == 0, FALSE )

By this, you do no longer need to check in the Conditional Split whether there is a NULL in MyColumn.

Answer 2

You are using the following expression in your conditional split component:

[Copy of RATE_INR] > 1000

As mentioned in the error message, this expression is evaluated as NULL which throws an exception since the conditional split component requires a boolean result (True/False).

You can solve this problem by using the REPLACENULL function:

REPLACENULL([Copy of RATE_INR],0) > 1000

Or

REPLACENULL([Copy of RATE_INR] > 1000, False)