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
