Min Max Transformation

Library references

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

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. Imports System Imports System.ComponentModel Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper Imports Microsoft.SqlServer.Dts.Runtime.Wrapper Imports CozyRoc.SqlServer.SSIS.Attributes <SortProperties(New String() {"Column", "Find", "ResultVariable"})> _ <ValidateProperties("Validate")> _ Public Class ScriptMain Inherits UserComponent Public Enum FindAction Minimum Maximum End Enum ' FindAction ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Public Overrides Sub Input_ProcessInputRow(ByVal Row As InputBuffer) Dim current As IComparable = CType(Row.Buffer(m_index), IComparable) If m_result Is Nothing Then m_result = current End If Select Case Me.Find Case FindAction.Minimum If m_result.CompareTo(current) > 0 Then m_result = current End If Case FindAction.Maximum If m_result.CompareTo(current) < 0 Then m_result = current End If Case Else Throw New Exception("Unhandled find action.") End Select End Sub ' Input_ProcessInputRow ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Public Overrides Sub PreExecute() Call MyBase.PreExecute() Dim input As IDTSInput90 = Me.ComponentMetaData.InputCollection(0) ' Find column index. m_index = Me.HostComponent.BufferManager.FindColumnByLineageID( _ input.Buffer, _ input.InputColumnCollection(Me.Column).LineageID) End Sub ' PreExecute ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Public Overrides Sub PostExecute() Call MyBase.PostExecute() ' Store result value in variable. Dim vars As IDTSVariables90 Call Me.VariableDispenser.LockOneForWrite(Me.ResultVariable, vars) Try vars(Me.ResultVariable).Value = m_result Finally Call vars.Unlock() End Try m_index = -1 End Sub ' PostExecute ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Public Function Validate(ByRef errMessage As String) As Boolean Dim result As Boolean = True Try If String.IsNullOrEmpty(Me.Column) Then Throw New Exception("Select input column.") ElseIf String.IsNullOrEmpty(Me.ResultVariable) Then Throw New Exception("Select 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 column. Dim valueColumn As IDTSInputColumn90 = managedComponent.SetUsageType( _ input.ID, _ virtInput, _ virtInput.VirtualInputColumnCollection(Me.Column).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 input column to search.")> _ <InputVirtualColumn()> _ Public Property Column() As String Get Column = m_column End Get Set(ByVal value As String) m_column = value End Set End Property ' Column ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' <Description("Select find action.")> _ Public Property Find() As FindAction Get Find = m_find End Get Set(ByVal value As FindAction) m_find = value End Set End Property ' Find ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' <Description("Select result variable.")> _ <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_column As String Private m_find As FindAction Private m_resultVar As String Private m_index As Integer Private m_result As IComparable #End Region ' Attributes End Class ' ScriptMain