SSIS Conditional Split NULL handling

1
votes

I am trying to update records from an SSIS conditional split to destinations after comparing them but I am stuck on NULL values. I have a source table with 128 columns that needs to be compared to destination to find records to update, unfortunately I cannot compare NULL values together or the result of the condition will be False as it cannot compare NULLs. Is there a way to easily compare Source and Destination records handling NULL values as string or any other way to avoid failure ? Thanks

1
sql-serverssisconditional-split
how do you compare those now? you could try use ISNULL(column,'NULL') to compare with the string NULL not actual NULL - LONG
Sure but how to handle it with a huge number of columns ? I will need to add 2 checks on each ? - OwenS
yes, you need to do that - LONG
I would read this question but now that I've seen the ridiculous comment I can't bring myself to read what the question is about, don't dump stuff like this in the comments: (SRC_ACT_PLN == DST_ACT_PLN) && (SRC_AG_ADD == DST_AG_ADD) && (SRC_AG_CITY == DST_AG_CITY)....... Add it to the question with an explanation. - Tanner
Sorry, need to start somewhere ... - OwenS

1 Answers

1
votes

Try use ISNULL(column,'NULL') to compare with the string NULL not actual NULL.

If both source and destination have NULL, you need to add ISNULL on both side, for example, ISNULL(Source_col,'NULL') = ISNULL(Target_col,'NULL')

Also, if you do not need to compare those NULL, you could filter out those NULL first, and it will grant your a better performance during the runtime.

For example, you could conditional split the source where col is not null and set the destination select * From table where col1 is not null and col2 is not null, etc.

UPDATE

In your case, it will be:

(ISNULL(SRC_ACT_PLN,'NULL') == ISNULL(DST_ACT_PLN,'NULL')) (same for the others) , but keep in mind that if both source and destination are NULL, it will still be returned as TRUE, otherwise, do what I suggest above, handle(filter out) NULL first then do the comparison.