Levenshtein Distance Transformation

Script File:

Overview

Data flow transformation, calculating the difference between two strings. The used algorithm is the famous Levenshtein Distance.

The script has the following parameters:

FirstColumn – specifies first column input.
SecondColumn – specifies second column input.
DistanceOutputColumn – specifies the name of output column.

Requirements

Depends on CozyRoc Script Component Plus component for implementing parameters user interface.
Requires installation of CozyRoc SSIS+ 1.4 library.

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://www.merriampark.com/ld.htm Imports System Imports System.ComponentModel Imports System.Math Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper Imports Microsoft.SqlServer.Dts.Runtime.Wrapper Imports CozyRoc.SqlServer.SSIS.Attributes ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' <SortProperties(New String() {"FirstColumn", "SecondColumn", "DistanceOutputColumn"})> _ <ValidateProperties("Validate")> _ Public Class ScriptMain Inherits UserComponent ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Public Overrides Sub Input_ProcessInputRow(ByVal Row As InputBuffer) Row.Buffer(m_distanceIndex) = GetLevenshteinDistance_( _ Row.Buffer(m_firstIndex).ToString(), _ Row.Buffer(m_secondIndex).ToString()) End Sub ' Input_ProcessInputRow ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Public Overrides Sub PreExecute() Call MyBase.PreExecute() Dim input As IDTSInput90 = Me.ComponentMetaData.InputCollection(0) Dim output As IDTSOutput90 = Me.ComponentMetaData.OutputCollection(0) ' Find first column index. m_firstIndex = Me.HostComponent.BufferManager.FindColumnByLineageID( _ input.Buffer, _ input.InputColumnCollection(Me.FirstColumn).LineageID) ' Find second column index. m_secondIndex = Me.HostComponent.BufferManager.FindColumnByLineageID( _ input.Buffer, _ input.InputColumnCollection(Me.SecondColumn).LineageID) ' Find distance column index. m_distanceIndex = Me.HostComponent.BufferManager.FindColumnByLineageID( _ input.Buffer, _ output.OutputColumnCollection(Me.DistanceOutputColumn).LineageID) End Sub ' PreExecute ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Public Overrides Sub PostExecute() Call MyBase.PostExecute() m_firstIndex = -1 m_secondIndex = -1 m_distanceIndex = -1 End Sub ' PostExecute ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Public Function Validate(ByRef errMessage As String) As Boolean Dim result As Boolean = True Try If String.IsNullOrEmpty(Me.FirstColumn) Then Throw New Exception("Select first string column.") End If If String.IsNullOrEmpty(Me.SecondColumn) Then Throw New Exception("Select second string column.") End If If String.IsNullOrEmpty(Me.DistanceOutputColumn) Then Throw New Exception("Specify distance output 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 first column. Dim firstColumn As IDTSInputColumn90 = managedComponent.SetUsageType( _ input.ID, _ virtInput, _ virtInput.VirtualInputColumnCollection(Me.FirstColumn).LineageID, _ DTSUsageType.UT_READONLY) ' Setup selected second column. Dim secondColumn As IDTSInputColumn90 = managedComponent.SetUsageType( _ input.ID, _ virtInput, _ virtInput.VirtualInputColumnCollection(Me.SecondColumn).LineageID, _ DTSUsageType.UT_READONLY) ' Setup distance output column. Dim output As IDTSOutput90 = Me.ComponentMetaData.OutputCollection(0) Try Dim column As IDTSOutputColumn90 = output.OutputColumnCollection(Me.DistanceOutputColumn) Catch ex As Exception ' Doesn't exist. Create it. Call output.OutputColumnCollection.RemoveAll() Dim distanceColumn As IDTSOutputColumn90 = output.OutputColumnCollection.[New]() distanceColumn.Name = Me.DistanceOutputColumn distanceColumn.SetDataTypeProperties(DataType.DT_I4, 0, 0, 0, 0) End Try Catch ex As Exception result = False errMessage = ex.Message End Try Validate = result End Function 'Validate #Region "Properties" ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' <Description("Select first string column.")> _ <Category("Input")> _ <InputVirtualColumn()> _ Public Property FirstColumn() As String Get FirstColumn = m_firstColumn End Get Set(ByVal value As String) m_firstColumn = value End Set End Property ' FirstColumn ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' <Description("Select second string column.")> _ <Category("Input")> _ <InputVirtualColumn()> _ Public Property SecondColumn() As String Get SecondColumn = m_secondColumn End Get Set(ByVal value As String) m_secondColumn = value End Set End Property ' SecondColumn ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' <Description("Specify distance output column name.")> _ <DefaultValue("DistanceColumn")> _ Public Property DistanceOutputColumn() As String Get DistanceOutputColumn = m_distanceColumn End Get Set(ByVal value As String) m_distanceColumn = value End Set End Property ' DistanceOutputColumn #End Region ' Properties #Region "Internals" ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' Compute the distance between two strings. ' Returns Levenshtein cost. Private Shared Function GetLevenshteinDistance_(ByVal s As String, ByVal t As String) As Integer Dim n As Integer = s.Length Dim m As Integer = t.Length Dim d(,) As Integer = New Integer(n, m) {} ' Step 1. If n = 0 Then Return m End If If m = 0 Then Return n End If ' Step 2. For i As Integer = 0 To n d(i, 0) = i Next For j As Integer = 0 To m d(0, j) = j Next ' Step 3. For i As Integer = 1 To n ' Step 4. For j As Integer = 1 To m ' Step 5. Dim cost As Integer = CInt(IIf(t(j - 1) = s(i - 1), 0, 1)) ' Step 6. d(i, j) = Min(Min(d(i - 1, j) + 1, d(i, j - 1) + 1), d(i - 1, j - 1) + cost) Next Next ' Step 7. GetLevenshteinDistance_ = d(n, m) End Function ' GetLevenshteinDistance_ #End Region ' Internals #Region "Attributes" Private m_firstColumn As String Private m_secondColumn As String Private m_distanceColumn As String Private m_firstIndex As Integer Private m_secondIndex As Integer Private m_distanceIndex As Integer #End Region ' Attributes End Class ' ScriptMain