]]> _
Public Class ScriptMain
Inherits UserComponent
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Public Overrides Sub ProcessInput( _
ByVal InputID As Integer, _
ByVal Buffer As Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer)
While Buffer.NextRow()
Dim groupValue As Object = Buffer(m_groupIndex)
If TypeOf groupValue Is BlobColumn Then
groupValue = Buffer.GetString(m_joinIndex)
End If
If groupValue Is Nothing Then
' Ignore NULL groups.
Continue While
End If
Dim joinValue As Object = Buffer(m_joinIndex)
If joinValue Is Nothing Then
joinValue = String.Empty
End If
If TypeOf joinValue Is BlobColumn Then
joinValue = Buffer.GetString(m_joinIndex)
End If
If m_currentGroup Is Nothing Then
' Init current group.
m_currentGroup = groupValue
End If
If Not m_currentGroup.Equals(groupValue) Then
' New group. Setup output record for existing data.
Call InsertJoinOutput_()
m_currentGroup = groupValue
End If
' Append join value to group.
If m_sbJoin.Length > 0 Then
Call m_sbJoin.Append(m_joinSymbol)
End If
Call m_sbJoin.Append(joinValue)
End While
If Buffer.EndOfRowset Then
Call InsertJoinOutput_()
Call MyBase.OutputBuffer.SetEndOfRowset()
End If
End Sub ' ProcessInput
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Public Overrides Sub PreExecute()
Call MyBase.PreExecute()
Dim input As IDTSInput90 = MyBase.ComponentMetaData.InputCollection(0)
' Find group column index.
m_groupIndex = MyBase.HostComponent.BufferManager.FindColumnByLineageID( _
input.Buffer, _
input.InputColumnCollection(Me.GroupColumn).LineageID)
' Find join column index.
m_joinIndex = MyBase.HostComponent.BufferManager.FindColumnByLineageID( _
input.Buffer, _
input.InputColumnCollection(Me.JoinColumn).LineageID)
' Get join symbol.
m_joinSymbol = Regex.Unescape(Me.JoinSymbol)
m_sbJoin = New StringBuilder()
End Sub ' PreExecute
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Public Function Validate(ByRef errMessage As String) As Boolean
Dim result As Boolean = True
Try
Dim input As IDTSInput90 = MyBase.ComponentMetaData.InputCollection(0)
Dim virtInput As IDTSVirtualInput90 = input.GetVirtualInput()
If virtInput.VirtualInputColumnCollection.Count > 0 AndAlso _
String.IsNullOrEmpty(Me.GroupColumn) Then
Throw New Exception("Select group column.")
End If
If virtInput.VirtualInputColumnCollection.Count > 0 AndAlso _
String.IsNullOrEmpty(Me.JoinColumn) Then
Throw New Exception("Select join column.")
End If
If String.IsNullOrEmpty(Me.JoinSymbol) Then
Throw New Exception("Specify join symbol.")
End If
Dim managedComponent As CManagedComponentWrapper = Me.ComponentMetaData.Instantiate()
Call input.InputColumnCollection.RemoveAll()
' Setup selected group column.
Dim groupColumn As IDTSInputColumn90 = managedComponent.SetUsageType( _
input.ID, _
virtInput, _
virtInput.VirtualInputColumnCollection(Me.GroupColumn).LineageID, _
DTSUsageType.UT_READONLY)
' Setup selected join column.
Dim joinColumn As IDTSInputColumn90 = managedComponent.SetUsageType( _
input.ID, _
virtInput, _
virtInput.VirtualInputColumnCollection(Me.JoinColumn).LineageID, _
DTSUsageType.UT_READONLY)
' Setup output columns for group and join column.
Dim output As IDTSOutput90 = MyBase.ComponentMetaData.OutputCollection(0)
Dim outColumn As IDTSOutputColumn90
If output.OutputColumnCollection.Count = 0 Then
' Setup group output column.
outColumn = output.OutputColumnCollection.[New]()
outColumn.Name = groupColumn.Name
' Setup join output column.
outColumn = output.OutputColumnCollection.[New]()
outColumn.Name = joinColumn.Name
Call outColumn.SetDataTypeProperties(DataType.DT_NTEXT, 0, 0, 0, 0)
End If
' Update group output column.
outColumn = output.OutputColumnCollection(0)
outColumn.Name = groupColumn.Name
Call outColumn.SetDataTypeProperties( _
groupColumn.DataType, _
groupColumn.Length, _
groupColumn.Precision, _
groupColumn.Scale, _
groupColumn.CodePage)
' Update group output column name.
outColumn = output.OutputColumnCollection(1)
outColumn.Name = joinColumn.Name
Catch ex As Exception
result = False
errMessage = ex.Message
End Try
Validate = result
End Function 'Validate
#Region "Properties"
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
_
_
Public Property GroupColumn() As String
Get
GroupColumn = m_groupColumn
End Get
Set(ByVal value As String)
m_groupColumn = value
End Set
End Property ' GroupColumn
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
_
_
Public Property JoinColumn() As String
Get
JoinColumn = m_joinColumn
End Get
Set(ByVal value As String)
m_joinColumn = value
End Set
End Property ' JoinColumn
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
_
Public Property JoinSymbol() As String
Get
JoinSymbol = m_joinSymbolRE
End Get
Set(ByVal value As String)
m_joinSymbolRE = value
End Set
End Property ' JoinSymbol
#End Region ' Properties
#Region "Internals"
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Sub InsertJoinOutput_()
With Me.OutputBuffer
Call .AddRow()
.Buffer(0) = m_currentGroup
.Buffer(1) = m_sbJoin.ToString()
End With
m_sbJoin.Length = 0
End Sub ' InsertJoinOutput_
#End Region ' Internals
#Region "Attributes"
Private m_groupColumn As String
Private m_joinColumn As String
Private m_joinSymbolRE As String
Private m_groupIndex As Integer
Private m_joinIndex As Integer
Private m_joinSymbol As String
Private m_currentGroup As Object
Private m_sbJoin As StringBuilder
#End Region ' Attributes
End Class ' ScriptMain
]]>ScriptComponent_844bc124bab34b198ac335e4b25a29b7trueCozyRoc.SqlServer.SSIS.ScriptComponentHostPlus, CozyRoc.SSISPlus, Version=1.0.0.0, Culture=neutral, PublicKeyToken=16cf490bb80c34ea