by Julie Smith
So two days ago I posted this. It’s a way to generate an SSIS Expression for use in an Incremental Load’s Conditional Split. A friend had pointed out that this pattern was not the best– as NULL handling is not always as easy as replacing the NULL with what you might consider a safe value. I also got a very thoughtful comment on the post from a lovely gentlemen expressing the same concern. So obsessed, I went back to tinkering. I came up with ANOTHER expression (and consequently another T-SQL generator for it). I like this one a little better as it seems to me that it performs what is asked without introducing risk of replacing NULL values. So folks please read this, use it, bash it up and let me know what you think.
Here’s the new (to me, sure someone had already figured this out) NULL Handling expression for DELTA rows, using the column Color as an example:
This does NOT break the Conditional Split if there are NULLS. There can be NULLS in either the source or the destination or both and it does not break the pipeline. I love that.
How to read it from the left:
The whole expression will evaluate as TRUE and the row split into the Delta path when either the yellow portion or the Green and blue portion together evaluate to True.
The yellow highlighted expression asks: is either side NULL while the other is not? If yes, then evaluate to TRUE.
The Green highlighted section: Are both sides NOT NULL ? If yes, Then Blue highlighted section asks: are they unequal? If yes, then evaluate to True.
If both sides are non null, yet equal, or if both sides are NULL, then the condition is not met and the row is ignored. Just like we want it to be.
Now the SQL to generate the whole expression can be datatype agnostic. I love that too. Here is the SQL to generate the whole concatenated shebang for all of your columns:
Click on the SQL below to get a copy/past version 🙂