none
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.

    Friday, October 27, 2006 12:05 AM

Answers

  • 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
    
    Friday, October 27, 2006 3:15 AM

All replies

  • 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
    
    Friday, October 27, 2006 3:15 AM
  • 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

    Friday, October 27, 2006 4:37 AM