Conditional Split

Question

Post reply

Conditional Split

dnyanesh.09

SSC Enthusiast

Points: 145
More actions
December 28, 2010 at 7:38 am

#144521

Hi
I have created the table with EmailAddress column in SQL Server 2008 DataBase
Now I need to write the validation expression for Email from that column in conditional split (SSIS) to sort out proper output in destionation file.
Please suggest if any one knows.
Thanks in Advance!

Viewing 11 posts - 1 through 10 (of 10 total)

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

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

What is your expression to validate an e-mailaddress?

If it is not overly complex, you can use the string functions in the conditional split.

If it is very complex, you can use the script component. If configured properly, it can function as a conditional split. In the script component, you can use .NET functions to validate your e-mail addresses using regular expressions.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Phil Parkin SSC Guru Points: 246962 More actions · Answer 2

Koen (da-zero) (12/28/2010)
--

Nearly New Year = nearly new name? 🙂

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 3

Phil Parkin (12/28/2010)
Koen (da-zero) (12/28/2010)
--
Nearly New Year = nearly new name? 🙂

Well yeah, sort of 🙂

I'm making a smooth transition to my real name. Real real smooth 🙂

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

JustMarie SSCertifiable Points: 7771 More actions · Answer 4

OK - I'm going to put my (extremely simple) suggestion here. Obviously it's looking for the basics of an email address - @ and .XXX. Yes, addresses using 4 character TLDs could be added using an OR statement.

Look for the @ symbol using the FINDSTRING command and direct accordingly.

Look for the "." character in the correct position using the LEN and SUBSTRING commands and direct accordingly.

I would put this into 2 conditional splits, narrowing the records and redirecting each type of improperly formatted address.

I won't belabor the obvious by asking why validation isn't handled in the application layer since that wasn't defined as an option.

Hope this helps.

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 5

JustMarie (12/29/2010)
I would put this into 2 conditional splits, narrowing the records and redirecting each type of improperly formatted address.

Why two seperate conditional splits when you can use one conditional split with multiple conditions?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Phil Parkin SSC Guru Points: 246962 More actions · Answer 6

JustMarie (12/29/2010)
OK - I'm going to put my (extremely simple) suggestion here. Obviously it's looking for the basics of an email address - @ and .XXX. Yes, addresses using 4 character TLDs could be added using an OR statement.
Look for the @ symbol using the FINDSTRING command and direct accordingly.
Look for the "." character in the correct position using the LEN and SUBSTRING commands and direct accordingly.
I would put this into 2 conditional splits, narrowing the records and redirecting each type of improperly formatted address.
I won't belabor the obvious by asking why validation isn't handled in the application layer since that wasn't defined as an option.
Hope this helps.

Looking for the @ sign is one thing, but looking for a dot 'in the correct position' is something more complicated, I think, as I am not sure what 'the correct position' is. It's certainly not just the fourth character from the right (or all your .co.uk addresses become invalid). Then there is .name to consider. There may be more variation ...

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 7

Phil Parkin (12/29/2010)
or all your .co.uk addresses become invalid

Always the same with those British people 😉

On-topic: such a check normally requires regular expressions, unless your checking for example companies e-mailadresses, who all have the same format.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Phil Parkin SSC Guru Points: 246962 More actions · Answer 8

Koen (da-zero) (12/29/2010)
Phil Parkin (12/29/2010)
or all your .co.uk addresses become invalid
Always the same with those British people 😉
On-topic: such a check normally requires regular expressions, unless your checking for example companies e-mailadresses, who all have the same format.

Just being helpful and making sure that the world does not forget us 🙂

Also, many UK companies end with .co.uk (and many other countries have .co.xx suffixes too) rather than .com, so I'm afraid that you're incorrect - just this once 😎

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

dnyanesh.09 SSC Enthusiast Points: 145 More actions · Answer 9

yes I have tried with string functions in conditional split but...

we need to do number of validations and it will be very complex expression.

do u have any such code?

Thanks

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 10

dnyanesh.09 (12/29/2010)
yes I have tried with string functions in conditional split but...
we need to do number of validations and it will be very complex expression.
do u have any such code?
Thanks

Nope, I don't.

But I have some tips:

read this article: http://www.mssqltips.com/tip.asp?tip=2137&home

It's about making your own conditional split in the script component.

Use regular expressions in .NET to create your condition. It should be a lot easier then using substrings in the usual conditional split.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP