Soundex Transformation

Library references

• System
• System.Data
• Microsoft.SqlServer.TxScript
• Microsoft.SqlServer.PipelineHost
• Microsoft.SqlServer.DTSPipelineWrap
• Microsoft.SqlServer.DTSRuntimeWrap
• Microsoft.SqlServer.ManagedDTS
• CozyRoc.SSISPlus

ScriptMain

' CozyRoc SQL Server Integration Services user script component ' ' Copyright (c) 2006-2010 CozyRoc LLC ' ' Permission is hereby granted, free of charge, to any person ' obtaining a copy of this software and associated documentation ' files (the "Software"), to deal in the Software without ' restriction, including without limitation the rights to use, ' copy, modify, merge, publish, distribute, sublicense, and/or sell ' copies of the Software, and to permit persons to whom the ' Software is furnished to do so, subject to the following ' conditions: ' ' The above copyright notice and this permission notice shall be ' included in all copies or substantial portions of the Software. ' ' THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, ' EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES ' OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND ' NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT ' HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, ' WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING ' FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR ' OTHER DEALINGS IN THE SOFTWARE. ' The following resources were used as a reference: ' http://en.wikipedia.org/wiki/Soundex ' http://blogs.techrepublic.com.com/programming-and-development/?p=656 Imports System Imports System.ComponentModel Imports System.Text Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper Imports Microsoft.SqlServer.Dts.Runtime.Wrapper Imports CozyRoc.SqlServer.SSIS.Attributes ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' <ValidateProperties("Validate")> _ Public Class ScriptMain Inherits UserComponent ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Public Overrides Sub Input_ProcessInputRow(ByVal Row As InputBuffer) Row.Soundex = Soundex_(Row.Buffer(m_inputIndex).ToString()) End Sub ' Input_ProcessInputRow ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Public Overrides Sub PreExecute() Call MyBase.PreExecute() Dim input As IDTSInput90 = Me.ComponentMetaData.InputCollection(0) ' Find input column index. m_inputIndex = Me.HostComponent.BufferManager.FindColumnByLineageID( _ input.Buffer, _ input.InputColumnCollection(Me.InputColumn).LineageID) End Sub ' PreExecute ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Public Function Validate(ByRef errMessage As String) As Boolean Dim result As Boolean = True Try If String.IsNullOrEmpty(Me.InputColumn) Then Throw New Exception("Select input column.") End If Dim managedComponent As CManagedComponentWrapper = Me.ComponentMetaData.Instantiate() Dim input As IDTSInput90 = Me.ComponentMetaData.InputCollection(0) Dim virtInput As IDTSVirtualInput90 = input.GetVirtualInput() Call input.InputColumnCollection.RemoveAll() ' Setup selected input column. Dim inputColumn As IDTSInputColumn90 = managedComponent.SetUsageType( _ input.ID, _ virtInput, _ virtInput.VirtualInputColumnCollection(Me.InputColumn).LineageID, _ DTSUsageType.UT_READONLY) Catch ex As Exception result = False errMessage = ex.Message End Try Validate = result End Function 'Validate #Region "Properties" ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' <Description("Select input column.")> _ <InputVirtualColumn()> _ Public Property InputColumn() As String Get InputColumn = m_inputColumn End Get Set(ByVal value As String) m_inputColumn = value End Set End Property ' InputColumn #End Region ' Properties #Region "Internals" Private Function Soundex_(ByVal data As String) As String Dim result As StringBuilder = New StringBuilder() If Not String.IsNullOrEmpty(data) Then data = data.ToLower() Call result.Append(data(0)) Dim prevCode As Integer = -1 Dim length As Integer = data.Length For i As Integer = 1 To length - 1 Dim letter As Char = data(i) Dim code As Integer = -1 If "bfpv".IndexOf(letter) > -1 Then code = 1 ElseIf "cgjkqsxz".IndexOf(letter) > -1 Then code = 2 ElseIf "dt".IndexOf(letter) > -1 Then code = 3 ElseIf letter = "l"c Then code = 4 ElseIf "mn".IndexOf(letter) > -1 Then code = 5 ElseIf letter = "r"c Then code = 6 Else Continue For End If If code <> prevCode Then Call result.Append(code) End If If result.Length = 4 Then Exit For End If If code <> -1 Then prevCode = code End If Next End If If result.Length < 4 Then Call result.Append(New String("0"c, 4 - result.Length)) End If Soundex_ = result.ToString().ToUpper() End Function ' Soundex_ #End Region ' Internals #Region "Attributes" Private m_inputColumn As String Private m_inputIndex As Integer #End Region ' Attributes End Class ' ScriptMain