VB.net Regex Example In SSIS

Add a script component to your package. Here, I am identifying common problems when names are fed into our system. Namely, I am trying to flag types of capitalization issues so that I can better modify the names as they come in

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
        '
        If Row.lastname_IsNull Then
            Row.lastnametype = "Last Name NULL"
        Else

            Dim ln As String = Row.lastname.ToString()

            'Return codes
            ' -1 no last name
            ' 0 unknown
            ' 1 All Caps
            If ln.Length > 0 Then
                Dim re As New Regex("^[A-Z]+$")
                If re.IsMatch(ln) Then
                    Row.lastnametype = "All Caps"
                Else
                    re = New Regex("^[A-Z][a-z]*$")
                    If re.IsMatch(ln) Then
                        Row.lastnametype = "First Letter Cap"
                    Else
                        re = New Regex("^[A-Z][a-z]*[A-Z][a-z]*$")
                        If re.IsMatch(ln) Then
                            Row.lastnametype = "First Letter Cap, And Subsequent Cap"
                        Else
                            re = New Regex("^[a-z]+$")
                            If re.IsMatch(ln) Then
                                Row.lastnametype = "All Lowercase"
                            Else
                                re = New Regex("[A-Z]")
                                Dim remc As MatchCollection = re.Matches(ln)
                                If remc.Count >= 3 Then
                                    Row.lastnametype = "3+Caps"
                                Else
                                    re = New Regex("[A-Za-z]+[ -&]+[A-Za-z]+")
                                    If re.IsMatch(ln) Then
                                        Row.lastnametype = "Two Last Names"
                                    Else
                                        Row.lastnametype = "Other"
                                    End If
                                End If
                            End If



                        End If


                    End If
                End If
            Else
                Row.lastnametype = "No Last Name"
            End If
        End If

    End Sub

End Class

Additional Interesting Articles

Execute Files Remotely Using PSExec
t-SQL Cursor
SQL Create Procedure
SQL Benchmarking - Clear Cache
SQL Check Existence of Table or Temp Table
PHP All Over Again

©2008 AndrewKimball.com