Conditional Split fails if value is NULL in SSIS

Question

I am passing result of FULL Outer join to Conditional Split and Filtering Records on the basis of following rules . Basically both tables has same schema and Primarykey values are same.

a. If Primary key of Source is NULL
b. If Primary Key of Destination is NULL
c. If Source and Destination key matches. 

It works fine for (a) and (b) but fails for (c)

Source.Id == Destination.Id

and throws exception that condition evaluated as NULL where Boolean was expected. How i can make this work?

Conditional Split gets input from Merge Join and it's a FULL OUTER JOIN as i need FULL OUTER join results here

Answer 1

Your third condition should start with a ISNULL check again before you compare your values. Like the following:

!ISNULL(Source.Id) && !ISNULL(Destination.Id) && Source.Id == Destination.Id 

You need to handle every column that can be NULL in your condition. Since you are comparing Id's, another option would be:

(ISNULL(Source.Id) ? 0 : Source.Id) == (ISNULL(Destination.Id) ? 0 : Destination.Id) 

If comparing strings, you can replace the zeroes with blank spaces.

Answer 2

Alternatively you can use the following syntax:

REPLACENULL(Source.Id,0) == REPLACENULL(Destination.Id,0)