Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS conditional split expression


SSIS conditional split expression

Author
Message
saurabh Srivastava-398090
saurabh Srivastava-398090
SSC-Enthusiastic
SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)

Group: General Forum Members
Points: 142 Visits: 178
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
divyanth
divyanth
SSC Veteran
SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)

Group: General Forum Members
Points: 215 Visits: 556
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
Todasdasdfd asfasf
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 109
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
Are you doing Data Warehouse ETL with SSIS? Don't use the slow SCD Wizard, use the Kimball Method SCD component instead.
JustMarie
JustMarie
SSC-Addicted
SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)

Group: General Forum Members
Points: 495 Visits: 1120
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search