Answered by:
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??????Tuesday, February 19, 2013 9:48 PM
Answers
-
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
- Proposed as answer by DeviantLogic Tuesday, February 19, 2013 11:51 PM
- Marked as answer by soldierfc Wednesday, February 20, 2013 2:39 PM
- Unmarked as answer by soldierfc Wednesday, February 20, 2013 3:37 PM
- Marked as answer by soldierfc Wednesday, February 20, 2013 4:09 PM
Tuesday, February 19, 2013 10:00 PM -
All replies
-
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
- Proposed as answer by DeviantLogic Tuesday, February 19, 2013 11:51 PM
- Marked as answer by soldierfc Wednesday, February 20, 2013 2:39 PM
- Unmarked as answer by soldierfc Wednesday, February 20, 2013 3:37 PM
- Marked as answer by soldierfc Wednesday, February 20, 2013 4:09 PM
Tuesday, February 19, 2013 10:00 PM -
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:
- Paid 3rd party tools to parse and clean-up addresses such as Melissa Data, Cozy Roc, Pragmatic Works Task Factory, etc.
- Free 3rd party address parsing components from Codeplex
- Script Component with Regular Expressions (Regex)
- Data Quality Services (SSIS 2012 DQS Component)
- Master Data Services business rules actions (using "must contain the pattern" validation action)
- 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.aspxDQS Integration with SSIS: Data Cleansing Using SSIS
http://technet.microsoft.com/en-us/sqlserver/hh828792.aspxPattern Matching in Search Conditions
http://msdn.microsoft.com/en-us/library/ms187489(SQL.90).aspxBusiness Rule Actions (Master Data Services)
http://msdn.microsoft.com/en-us/library/hh231098.aspxRegular Expressions Make Pattern Matching And Data Extraction Easier
http://msdn.microsoft.com/en-us/magazine/cc163473.aspxWednesday, February 20, 2013 1:53 AM -
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.
- Edited by soldierfc Wednesday, February 20, 2013 3:38 PM
Wednesday, February 20, 2013 3:36 PM -