November 29, 2007 at 9:58 am
Hello
Anyone has a function i can call in SSIS in order to "clean" the email adress before inserting into the database?
One of the rule could be to verify at least that the adress should be in the format a@b.c
What other rules could there be? smthg that checks the domain names etc...
thanks!
November 29, 2007 at 3:07 pm
Use regular expressions within CLR. Here is a link to a blog with a specific example (validating an e-mail address even :))
http://blogs.mscommunity.net/blogs/tkralj/default.aspx
Tommy
Follow @sqlscribeNovember 29, 2007 at 3:32 pm
You must use Script Component with transformation type, two outputs with SynchronusInputID set to none (for example ValidEMail and NotValidEMail) and with one column for each output (ValidEmail for ValidEMail output and NotValidemail for NotValiedEMail output - each column type DT_STR). On the input of this component is path from your data source, and each output from this component is redirected to two destinations: one for valid email addresses and the second for bad email addresses.
Here is the code for script component:
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports System.Text.RegularExpressions
Public Class ScriptMain
Inherits UserComponent
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
'
' Add your code here
'
Dim e_mail_validator As String = "^([\w\-\.]+)@((\[([0-9]{1,3}\.){3}[0-9]{1,3}\])|(([\w\-]+\.)+)([a-zA-Z]{2,4}))$"
Dim oReg As Regex = New Regex(e_mail_validator)
If (Not oReg.Match(Row.E_mail).Success) Then
ValidBuffer.AddRow()
ValidBuffer.Validemail = Row.E_mail
Else
NotValidBuffer.AddRow()
NotValidBuffer.NotValidemail = Row.E_mail
End If
End Sub
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply