Conditional Split - Expression Evaluates to Null

Hi everyone!

I'm using a conditional split to discriminate modified records. My expression looks like this:

col1_source != col1_dest || col2_source != col2_des.....and so on. I use OLE DB Command afterward to update modified records.

It all works fine if no columns evaluate to null. If any (source or dest.) evaluates to null, component fails.

Any tips how to solve a problem?

It has to work like this:

If colX_source is null and colX_dest is not null --> Update

If colX_source is not null and colX_dest is null --> Update

If both colX_source and colX_dest are null --> No update

p.s. i apologize if a similar thread exists, I haven't found something of use to me.

Use the ISNULL() function to test your columns for NULLs.

For instance:

ISNULL(colX_source) && !ISNULL(colX_dest)

|||

Simply add to your expression:

(IsNull(colX_Source) && IsNull(colX_dest)) || ((IsNull(colX_source) == false) && (IsNull(colX_dest)))

etc...

Or, use a derived column before the conditional split to eliminate the nulls:

Output1 / add as new column / IsNull(colX_Source) ? "" : colX_Source

|||

Thank you both for a quick response.

I understood the derived column idea but not the isnull idea.

If I want to cover all the cases in which I want the update to execute, my expression (if I haven’t missunderstood sth) should look like this:

isNull(col1_source) && !IsNull(col1_dest) || !isNull(col1_source) && IsNull(col1_dest) || col1_source != col1_dest

Isn’t it true that again, the last part of the expression col1_source != col1_dest evaluates to null if one of the columns is null? And that the only solution to the problem is a derived column which transforms null values in “”?

I’m new to SSIS and slowly learning that programming logic is not always the SSIS logic.

|||

The way it is written, it will only get to the last branch if both columns are NULL, which would render the last branch a moot point anyway because they would have to be "equal."

Use the derived column idea to change the NULLs to empty strings and then your conditional split will be easier to write/maintain. Plus, if you're not supposed to have NULL data, then it would be the proper thing to do to clean it up.

|||

I think my initial post is a little misguiding because I omitted a condition I have to test (which in fact is the last branch (a moot point as you say )).

If both colX_source and colX_dest are not null and different --> also Update!

+ the one I mentioned in the 1st post:

If colX_source is null and colX_dest is not null --> Update

If colX_source is not null and colX_dest is null --> Update

If both colX_source and colX_dest are null --> No update

|||( !ISNULL(colX_source) && !ISNULL(colX_dest) ) && (colX_source != colX_dest)|||

Wow, it works! Thank you for your patience!