Counter 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-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 Microsoft.SqlServer.Dts.Pipeline.Wrapper Imports Microsoft.SqlServer.Dts.Runtime.Wrapper Imports CozyRoc.SqlServer.SSIS.Attributes ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' <SortProperties(New String() {"CounterStart", "CounterOutputColumn"})> _ <ValidateProperties("Validate")> _ Public Class ScriptMain Inherits UserComponent ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Public Overrides Sub Input_ProcessInputRow(ByVal Row As InputBuffer) Row.Buffer.Item(m_counterIndex) = m_counter m_counter += 1 End Sub ' Input_ProcessInputRow ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Public Overrides Sub PreExecute() Call MyBase.PreExecute() ' Load counter start. Dim vars As IDTSVariables90 Me.VariableDispenser.LockOneForRead(Me.CounterStart, vars) Try m_counter = CInt(vars(Me.CounterStart).Value) Finally Call vars.Unlock() End Try Dim input As IDTSInput90 = Me.ComponentMetaData.InputCollection(0) Dim output As IDTSOutput90 = Me.ComponentMetaData.OutputCollection(0) ' Find counter column index. m_counterIndex = Me.HostComponent.BufferManager.FindColumnByLineageID( _ input.Buffer, _ output.OutputColumnCollection(Me.CounterOutputColumn).LineageID) End Sub ' PreExecute ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Public Function Validate(ByRef errMessage As String) As Boolean Dim result As Boolean Try If String.IsNullOrEmpty(Me.CounterStart) Then Throw New ApplicationException("Select counter start variable.") End If If String.IsNullOrEmpty(Me.CounterOutputColumn) Then Throw New ApplicationException("Specify output column for counter.") End If ' Setup counter output column. Dim output As IDTSOutput90 = Me.ComponentMetaData.OutputCollection(0) Try Dim column As IDTSOutputColumn90 = output.OutputColumnCollection(Me.CounterOutputColumn) Catch ex As Exception ' Doesn't exist. Create it. Call output.OutputColumnCollection.RemoveAll() Dim counterColumn As IDTSOutputColumn90 = output.OutputColumnCollection.[New]() counterColumn.Name = Me.CounterOutputColumn counterColumn.SetDataTypeProperties(DataType.DT_I4, 0, 0, 0, 0) End Try result = True Catch ex As Exception result = False errMessage = ex.Message End Try Validate = result End Function 'Validate #Region "Properties" ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' <Description("Select variable containing counter start.")> _ <Variable()> _ Public Property CounterStart() As String Get CounterStart = m_counterStart End Get Set(ByVal value As String) m_counterStart = value End Set End Property ' CounterStart ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' <Description("Specify counter output column name.")> _ <DefaultValue("CounterColumn")> _ Public Property CounterOutputColumn() As String Get CounterOutputColumn = m_counterColumn End Get Set(ByVal value As String) m_counterColumn = value End Set End Property ' CounterOutputColumn #End Region ' Properties #Region "Attributes" Private m_counterStart As String Private m_counterColumn As String Private m_counter As Integer Private m_counterIndex As Integer #End Region ' Attributes End Class ' ScriptMain