Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSIS conditional split expression Expand / Collapse
Author
Message
Posted Thursday, March 18, 2010 2:01 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, December 16, 2013 2:48 AM
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
Post #885305
Posted Thursday, March 18, 2010 10:13 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, October 17, 2013 4:51 PM
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")
Post #885690
Posted Thursday, March 18, 2010 10:19 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 22, 2010 9:44 AM
Points: 36, 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.
Post #885698
Posted Thursday, March 18, 2010 3:17 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 7:49 AM
Points: 273, Visits: 613
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.
Post #885944
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse