Conditional Split Case Statement Problems??????

Question

How can I use a conditional split to verify many multiple conditions in one Case?  Is it possible to check for multiple conditions in one case or do I need to come up with different cases? Reason being if any of these instances are true I am going to insert the record into a particular .  I don't think it is good to have 13 different inserts for the same table, and 13 different conditions.  For example the address column I have needs to not be null and it needs to have either 'A-Z', 'a-z', and '0-9'.  How can I accomplish this because just checking for nulls creates separate cases.  Why can't I use the || to mean or and just create one long case??????

Answer 1

Chained Conditional Operators

«boolean_test» ? «true_result» : «false_result»

will work, but they must be placed logically correct which may be an issue at times

---

Arthur My Blog

Answer 2

I wouldn't use Conditional Split for this. I would put as much logic and clean-up in your source query using T-SQL rather than with the SSIS Expression Language. Perhaps, you could base your data source from a stored procedure that cleanses the data or even tags the rows with an "IsValidAddress" TRUE/FALSE column . As an alternative you could also accomplish this with:

1. Paid 3rd party tools to parse and clean-up addresses such as Melissa Data, Cozy Roc, Pragmatic Works Task Factory, etc.
2. Free 3rd party address parsing components from Codeplex
3. Script Component with Regular Expressions (Regex)
4. Data Quality Services (SSIS 2012 DQS Component)
5. Master Data Services business rules actions (using "must contain the pattern" validation action)
6. CLR function and Regular Expressions (Regex)

References:

SSIS Community Tasks and Components
http://ssisctc.codeplex.com/

Pattern matching in SSIS using Regular Expressions and the Script component
http://www.purplefrogsystems.com/blog/2011/07/pattern-matching-in-ssis-using-regular-expressions-and-the-script-component/

Data Cleansing (Data Quality Services)
http://msdn.microsoft.com/en-us/library/gg524800.aspx

DQS Integration with SSIS: Data Cleansing Using SSIS
http://technet.microsoft.com/en-us/sqlserver/hh828792.aspx

Pattern Matching in Search Conditions
http://msdn.microsoft.com/en-us/library/ms187489(SQL.90).aspx

Business Rule Actions (Master Data Services)
http://msdn.microsoft.com/en-us/library/hh231098.aspx

Regular Expressions Make Pattern Matching And Data Extraction Easier
http://msdn.microsoft.com/en-us/magazine/cc163473.aspx

Answer 3

Chained Conditional Operators

«boolean_test» ? «true_result» : «false_result»

will work, but they must be placed logically correct which may be an issue at times

---

Arthur My Blog

Good Answer:

How do I get this ==> FINDSTRING([Copy of Copy of EMAIL_ADDRESS],"@",1) > 0 to test as a boolean?  I want this to test the email column and if it doesn't have the @ symbol it is  invalid.

Answer 4

FINDSTRING([Copy of Copy of EMAIL_ADDRESS],"@",1) > 0 already produces true or false

---

Arthur My Blog