Friday, March 9, 2012

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

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.

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

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

No comments:

Post a Comment