SSIS finding NULL's and empty fields and renaming them

Question

I'm trying to find NULL values within my database column with SSIS (2008 R2 Enterprise), but it gives me the following error:

The expression "FINDSTRING([Copy of jaartext],"",1) > 0" on "output "Opdracht" (552)" evaluated to NULL, but the "component "Conditional Split" (336)" requires a Boolean results.

I'm trying to Conditional Split every options of my database (with the data type string) to a different output, as you see in figure 1 below. Then I'm using a Derived Column each of the options to REPLACE their value by an integer (see figure 2).

This all works, except for the NULL value. Any tips for me to look up the NULL value, instead of the empty string? I'd really appreciate some help.

enter image description here [Figure 1]

enter image description here [Figure 2]

billinkc · Accepted Answer · 2014-09-30 15:02:12Z

1

You'll want to use IsNull and the ternary operator (boolean) ?:

IsNull([ColumnName]) ? "-1" : REPLACE([ColumnName], " ", "1")

Here, if our column is NULL, then I assign -1. Otherwise, I replace a space with 1

answered Sep 30, 2014 at 15:02

billinkc

61.4k10 gold badges110 silver badges169 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

TMNuclear Over a year ago

Ah thanks a lot, that fixed my problem. Although I found a new one. ISNULL([Copy of jaartext]) finds all the NULL-valued records and pass them to a derived column, then I use the code you provided to replace the NULL's. However, FINDSTRING([Copy of jaartext],"",1) > 0 do not find the empty string values, which I'm trying to pass to a derived column as well. Now I'm trying to figure out why the empty string cannot be found. Perhaps you have a quick tip/solution for this as well - will not have to reopen a new question.

TMNuclear Over a year ago

I guess FINDSTRING doesn't work on blank fields. I got the condition to work with: TRIM([Copy of jaartext]) == "" ? "1" : [Copy of jaartext]

billinkc Over a year ago

I would generally use something like len(ltrim([MyColumn])) ==0 to determine whether a string is empty

Collectives™ on Stack Overflow

SSIS finding NULL's and empty fields and renaming them

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related