SSIS conditional split expression

Question

SSIS conditional split expression

saurabh Srivastava-398090

SSC-Addicted

Points: 402
More actions
March 18, 2010 at 2:01 am

#217911

I am trying to write an expression in conditional split transform to evaluate a condition: Here is the scenario.
COlumnA cannot be BLANK if the ColumnB has one of the value (Alpha,beta,Ray,john,text) in tableC.
Any help is appreciated

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

divyanth SSCarpal Tunnel Points: 4303 More actions · Answer 1

COlumnA cannot be BLANK if the ColumnB has one of the value (Alpha,beta,Ray,john,text)

I have always seen that conditional split acts weirdly when checking for NULL columns..I have also seen that ISNull() does not work correctly..

I would check for Length of ColumnA to determine if its a NULL Value

(LEN(TRIM(ColumnA))>1) && ( [ColumnB] =="Alpha" || [ColumnB] =="Beta" || [ColumnB] =="Ray" || [ColumnB] =="John" || [ColumnB] =="text")

Todasdasdfd asfasf Ten Centuries Points: 1340 More actions · Answer 2

You can only construct expressions in a Conditional Split that apply to the columns in the current data flow. So if you need to do a lookup to see if you can find whether a column in your flow matches something in another table, you'll have to do a lookup or other transformation before your conditional split.

@divyanth - ISNULL() works perfectly in SSIS. The problems I've seen people have with detecting "blanks" in columns is entirely related to their inability to understand the difference between NULL, a string with no characters in it, and a string with only spaces in it. (I'm being blunt - I mean no disrespect.) ISNULL detects NULL values. NULL values are different than an empty string or a string with only spaces in it - so ISNULL doesn't equate those to NULL.

Todd McDermid - SQL Server MVP, MCSD.Net, MCTS (SQL08BI)
Find great SSIS extensions - most are open source - at SSIS Community Tasks and Components[/url]
Are you doing Data Warehouse ETL with SSIS? Don't use the slow SCD Wizard, use the Kimball Method SCD component instead.

JustMarie SSCertifiable Points: 7771 More actions · Answer 3

One other gotcha on NULL values and the conditional split is that the NULL conditions need to be resolved before anything else. So if you've got multiple conditions anything with NULL needs to be first in the sequence.

I'm not sure that it applies here as much but I know I've used the ISNULL() in any number of splits without a problem. The only time it's been cranky is when I forgot to put the NULL conditions before the others.