Range Dictionary Destination

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-2009 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. Imports System Imports System.ComponentModel Imports System.Collections.Generic Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper Imports Microsoft.SqlServer.Dts.Runtime.Wrapper Imports CozyRoc.SqlServer.SSIS.Attributes ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' <SortProperties(New String() {"StartColumn", "EndColumn", "ValueColumn", "ResultVariable"})> _ <ValidateProperties("Validate")> _ Public Class ScriptMain Inherits UserComponent ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Public Overrides Sub Input_ProcessInputRow(ByVal Row As InputBuffer) Call m_list.Add(New RangeValue( _ Row.Buffer(m_startIndex), _ Row.Buffer(m_endIndex), _ Row.Buffer(m_valueIndex))) End Sub ' Input_ProcessInputRow ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Public Overrides Sub PreExecute() Call MyBase.PreExecute() ' Setup range list object. Dim vars As IDTSVariables90 Call Me.VariableDispenser.LockOneForWrite(Me.ResultVariable, vars) Try Try Dim rvd As RangeValueDictionary = CType( _ vars(Me.ResultVariable).Value, _ RangeValueDictionary) m_list = rvd.Ranges Catch ex As Exception ' Dictionary not yet initialized. m_list = New List(Of RangeValue) vars(Me.ResultVariable).Value = New RangeValueDictionary(m_list) End Try Finally Call vars.Unlock() End Try Dim input As IDTSInput90 = Me.ComponentMetaData.InputCollection(0) ' Find range start column index. m_startIndex = Me.HostComponent.BufferManager.FindColumnByLineageID( _ input.Buffer, _ input.InputColumnCollection(Me.StartColumn).LineageID) ' Find range end column index. m_endIndex = Me.HostComponent.BufferManager.FindColumnByLineageID( _ input.Buffer, _ input.InputColumnCollection(Me.EndColumn).LineageID) ' Find value column index. m_valueIndex = Me.HostComponent.BufferManager.FindColumnByLineageID( _ input.Buffer, _ input.InputColumnCollection(Me.ValueColumn).LineageID) End Sub ' PreExecute ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Public Overrides Sub PostExecute() Call MyBase.PostExecute() ' Sort for binary search to work. Call m_list.Sort() m_list = Nothing m_startIndex = -1 m_endIndex = -1 m_valueIndex = -1 End Sub ' PostExecute ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Public Function Validate(ByRef errMessage As String) As Boolean Dim result As Boolean = True Try If String.IsNullOrEmpty(Me.StartColumn) Then Throw New Exception("Select range start column.") ElseIf String.IsNullOrEmpty(Me.EndColumn) Then Throw New Exception("Select range end colum.") ElseIf String.IsNullOrEmpty(Me.ValueColumn) Then Throw New Exception("Select value colum.") ElseIf String.IsNullOrEmpty(Me.ResultVariable) Then Throw New Exception("Select dictionary result variable.") Else 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 range start column. Dim startColumn As IDTSInputColumn90 = managedComponent.SetUsageType( _ input.ID, _ virtInput, _ virtInput.VirtualInputColumnCollection(Me.StartColumn).LineageID, _ DTSUsageType.UT_READONLY) ' Setup selected range start column. Dim endColumn As IDTSInputColumn90 = managedComponent.SetUsageType( _ input.ID, _ virtInput, _ virtInput.VirtualInputColumnCollection(Me.EndColumn).LineageID, _ DTSUsageType.UT_READONLY) ' Setup selected value column. Dim valueColumn As IDTSInputColumn90 = managedComponent.SetUsageType( _ input.ID, _ virtInput, _ virtInput.VirtualInputColumnCollection(Me.ValueColumn).LineageID, _ DTSUsageType.UT_READONLY) End If Catch ex As Exception result = False errMessage = ex.Message End Try Validate = result End Function ' Validate #Region "Properties" ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' <Description("Select range start column.")> _ <Category("Input")> _ <InputVirtualColumn()> _ Public Property StartColumn() As String Get StartColumn = m_startColumn End Get Set(ByVal value As String) m_startColumn = value End Set End Property ' StartColumn ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' <Description("Select range end column.")> _ <Category("Input")> _ <InputVirtualColumn()> _ Public Property EndColumn() As String Get EndColumn = m_endColumn End Get Set(ByVal value As String) m_endColumn = value End Set End Property ' EndColumn ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' <Description("Select value column.")> _ <Category("Input")> _ <InputVirtualColumn()> _ Public Property ValueColumn() As String Get ValueColumn = m_valueColumn End Get Set(ByVal value As String) m_valueColumn = value End Set End Property ' ValueColumn ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' <Description("Select variable with dictionary object.")> _ <Category("Result")> _ <Variable()> _ Public Property ResultVariable() As String Get ResultVariable = m_resultVar End Get Set(ByVal value As String) m_resultVar = value End Set End Property ' ResultVariable #End Region ' Properties #Region "Attributes" Private m_startColumn As String Private m_endColumn As String Private m_valueColumn As String Private m_resultVar As String Private m_list As List(Of RangeValue) Private m_startIndex As Integer Private m_endIndex As Integer Private m_valueIndex As Integer #End Region ' Attributes End Class ' ScriptMain

RangeDictionary

' CozyRoc SQL Server Integration Services user script component ' ' Copyright (c) 2006-2009 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. Imports System Imports System.Collections Imports System.Collections.Generic ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' Container class for range value. Public Class RangeValue Implements IComparable(Of RangeValue) ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Public Sub New(ByVal startR As Object, ByVal endR As Object, ByVal value As Object) m_start = CType(startR, IComparable) m_end = CType(endR, IComparable) m_value = value End Sub ' RangeValue ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Public Function CompareTo(ByVal other As RangeValue) As Integer Implements IComparable(Of RangeValue).CompareTo Dim compareStart As Integer = m_start.CompareTo(other.m_start) Dim compareEnd As Integer = m_end.CompareTo(other.m_end) If compareStart < 0 AndAlso compareEnd < 0 Then Return -1 End If If compareStart > 0 AndAlso compareEnd > 0 Then Return 1 End If If compareStart = 0 AndAlso compareEnd = 0 Then Return 0 End If Throw New ArgumentException("Overlapping Range.") End Function ' CompareTo ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' Returns: ' 0 if value is in the specified range. ' < 0 if value is below the range. ' > 0 if value is above the range. Public Function CompareTo(ByVal key As Object) As Integer Dim compareStart As Integer = m_start.CompareTo(key) Dim compareEnd As Integer = m_end.CompareTo(key) If compareStart <= 0 AndAlso compareEnd >= 0 Then Return 0 ElseIf compareEnd < 0 Then Return -1 End If Return 1 End Function ' CompareTo #Region "Properties" ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Public ReadOnly Property Value() As Object Get Return m_value End Get End Property ' Value #End Region ' Properties #Region "Attributes" Private m_start As IComparable Private m_end As IComparable Private m_value As Object #End Region ' Attributes End Class ' RangeValue ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' The only implemented IDictionary interface methods are: Contains, Item Get ' ' Uses binary search to find value in specialized Ranges array. Public Class RangeValueDictionary Implements IDictionary ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Public Sub New(ByVal list As List(Of RangeValue)) m_list = list End Sub ' RangeValueDictionary ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Public Sub CopyTo(ByVal array As Array, ByVal index As Integer) Implements ICollection.CopyTo Throw New Exception("Not Implemented.") End Sub ' CopyTo ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Public Sub Add(ByVal key As Object, ByVal value As Object) Implements IDictionary.Add Throw New Exception("Not Implemented.") End Sub ' Add ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Public Sub Clear() Implements IDictionary.Clear Throw New Exception("Not Implemented.") End Sub ' Clear ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Public Function Contains(ByVal key As Object) As Boolean Implements IDictionary.Contains Contains = GetKeyIndex_(key) >= 0 End Function ' Contains ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Public Function GetEnumerator() As IDictionaryEnumerator Implements IDictionary.GetEnumerator Throw New Exception("Not Implemented.") End Function ' GetEnumerator ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Public Sub Remove(ByVal key As Object) Implements IDictionary.Remove Throw New Exception("Not Implemented.") End Sub ' Remove ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Public Function GetEnumerator1() As IEnumerator Implements IEnumerable.GetEnumerator Throw New Exception("Not Implemented.") End Function ' GetEnumerator1 #Region "Properties" Public ReadOnly Property Ranges() As List(Of RangeValue) Get Return m_list End Get End Property ' Ranges ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Public ReadOnly Property Count() As Integer Implements ICollection.Count Get Throw New Exception("Not Implemented.") End Get End Property ' Count ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Public ReadOnly Property IsSynchronized() As Boolean Implements ICollection.IsSynchronized Get IsSynchronized = False End Get End Property ' IsSynchronized ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Public ReadOnly Property SyncRoot() As Object Implements ICollection.SyncRoot Get Return m_list End Get End Property ' SyncRoot ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Public ReadOnly Property IsFixedSize() As Boolean Implements IDictionary.IsFixedSize Get IsFixedSize = True End Get End Property ' IsFixedSize ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Public ReadOnly Property IsReadOnly() As Boolean Implements IDictionary.IsReadOnly Get IsReadOnly = True End Get End Property ' IsReadOnly ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Default Public Property Item(ByVal key As Object) As Object Implements IDictionary.Item Get Dim index As Integer = GetKeyIndex_(key) Dim rangeValue As RangeValue = m_list(index) Return rangeValue.Value End Get Set(ByVal value As Object) Throw New Exception("Not Implemented.") End Set End Property ' Item ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Public ReadOnly Property Keys() As ICollection Implements IDictionary.Keys Get Throw New Exception("Not Implemented.") End Get End Property ' Keys ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Public ReadOnly Property Values() As ICollection Implements IDictionary.Values Get Throw New Exception("Not Implemented.") End Get End Property ' Values #End Region ' Properties #Region "Internals" ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Private Function GetKeyIndex_(ByVal key As Object) As Integer If Not m_cachedKey Is Nothing AndAlso m_cachedKey.CompareTo(key) = 0 Then ' Searched key index is already cached. Return m_cachedIndex End If m_cachedKey = CType(key, IComparable) m_cachedIndex = BinarySearch_(key) GetKeyIndex_ = m_cachedIndex End Function ' GetKeyIndex_ ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' Binary search for specified key. Private Function BinarySearch_(ByVal key As Object) As Integer Dim min As Integer = 0 Dim max As Integer = m_list.Count - 1 Do While min <= max Dim mid As Integer = (min + max) >> 1 Dim compare As Integer = m_list(mid).CompareTo(key) If compare = 0 Then Return mid End If If compare < 0 Then min = mid + 1 Else max = mid - 1 End If Loop Return Not min End Function ' BinarySearch_ #End Region ' Internals #Region "Attributes" Private m_list As List(Of RangeValue) Private m_cachedKey As IComparable Private m_cachedIndex As Integer #End Region ' Attributes End Class ' RangeDictionary