Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer column ordinal from name?

Question

Hi,

I need to access columns from a data flow by ordinal position in a script transformation (I'm parsing an excel file which has several rowsets across the page). The first problem I encountered is the generated BufferWrapper does not expose the columns collection (i.e. Input0Buffer(0) does not work) but I got around that by implementing my own ProcessInputs(InputId, Buffer) method instead of using the wrapper.

My problem now is that the column ordinals are in some random order (i.e. Column "F1" is ordinal 1 but Column "F2" is 243). Where in the object model can I map between the name and the ordinal - it's not jumping out at me?

Dave

 

PS Why is the script editor modal, it's frustrating having to switch between the Visual Studio environment and the VSA one.

Answer 1

To read and write buffer columns by ordinal position in a script transform, load a name => index dictionary in the PreExecute function.  The dictionary key would be the column name, and the dictionary value a structure containing column metadata, including the buffer column index you're referring to, and whatever else you'd like.

In PreExecute() function, load up the dictionary.  It will then be available for use when you require direct column access by ordinal position.  Here's an example which uses the GetColumnIndexes in lieu of BufferManager.FindColumnByLineageID().

Imports System
Imports System.Data
Imports System.Math
Imports System.Collections.Generic
Imports Microsoft.SqlServer.Dts.Pipeline
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain
    Inherits UserComponent

    Private inputBuffer As PipelineBuffer
    Private cols As Dictionary(Of String, ColumnInfo) = New Dictionary(Of String, ColumnInfo)
    Private currentColumnInfo As ColumnInfo = New ColumnInfo

    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
        If cols.TryGetValue("GeneratedStr_1", currentColumnInfo) Then
            ' retrieve column metatdata by column name
            inputBuffer.SetString(currentColumnInfo.colIndex, Guid.NewGuid().ToString())
        End If
    End Sub

    Public Overrides Sub ProcessInput(ByVal InputID As Integer, ByVal Buffer As Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer)
        ' Get the Pipeline Buffer for subsequent ordinal column access
        inputBuffer = Buffer
        MyBase.ProcessInput(InputID, Buffer)
    End Sub

    Public Overrides Sub PreExecute()
        BuildColumnDictionary()
    End Sub

    Private Sub BuildColumnDictionary()
        Dim indexes() As Integer
        Dim input As IDTSInput90
        Dim col As IDTSInputColumn90
        Dim offset As Integer = 0

        input = Me.ComponentMetaData.InputCollection(0)
        'presumes GetColumnIndexes order matches iterator order
        'as BufferManager is not available to my knowledge in ScriptComponent
        indexes = Me.GetColumnIndexes(input.ID)
        For Each col In input.InputColumnCollection
            Dim columnStructure As New ColumnInfo
            With columnStructure
                .colName = col.Name
                .colLength = col.Length
                .colIndex = indexes(offset)
                'Normally, BufferManager would be used, but its not exposed in Script Component
                .colPrecision = col.Precision
                .colScale = col.Scale
                .colType = col.DataType
            End With
            Me.Log(String.Format("Name {0} Buffer Index {1} offset {2} ", col.Name, indexes(offset), offset), 0, Nothing)
            cols.Add(col.Name, columnStructure)
            offset += 1
        Next
    End Sub

    Public Structure ColumnInfo
        Dim colName As String
        Dim colType As DataType
        Dim colIndex As Int32
        Dim colLength As Int32
        Dim colPrecision As Int32
        Dim colScale As Int32
    End Structure

End Class

Answer 2

Hi Jaegd

Thanks for the code, capturing the PipelineBuffer in ProcessInputs for later use in _ProcessInputRow is a good idea. I'd also not noticed that PipelineBuffer has Get{Type} \ Set{Type} methods.

Your assumption that the 'GetColumnIndexes order matches iterator order' does not appear to hold though (at least for the Excel Data Source) but I found an alternative that appears to be ok

With columnStructure
                .colName = col.Name
                .colLength = col.Length
                .colIndex = input.InputColumnCollection.GetObjectIndexByID(col.ID)

                .colPrecision = col.Precision
                .colScale = col.Scale
                .colType = col.DataType
End With

Dave