SSIS Script Component Transformation: Split record into multiple records (ver. one common column)

Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper Imports Microsoft.SqlServer.Dts.Runtime.Wrapper Imports System.Reflection Imports Microsoft.SqlServer.Dts.Pipeline _ _ Public Class ScriptMain Inherits UserComponent Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer) Dim column As IDTSInputColumn100 Dim rowType As Type = Row.GetType() Dim columnValue As PropertyInfo Dim rok As Integer Dim colName As String Dim regon As String Dim arr() As String Dim farr As New DataTable() 'Dim ds As New DataSet() Dim i As Integer = 0 Dim j As Decimal = Nothing farr.Columns.Add("cName", GetType(String)) farr.Columns.Add("cValue", GetType(String)) farr.Columns.Add("qtr", GetType(Integer)) For Each column In Me.ComponentMetaData.InputCollection(0).InputColumnCollection i = 0 columnValue = rowType.GetProperty(column.Name) If column.Name.ToString() = "rok" Then rok = columnValue.GetValue(Row, Nothing) ElseIf column.Name.ToString() = "regon" Then regon = columnValue.GetValue(Row, Nothing) Else colName = column.Name 'ds.Tables.Add(colName) ' If Not farr.Columns.Contains(column.Name) Then ' farr.Columns.Add(colName) 'End If arr = columnValue.GetValue(Row, Nothing).ToString.Split(";") For Each s In arr i = i + 1 farr.Rows.Add(colName, s, i) Next ReDim arr(0) i = 0 End If Next For Each r As DataRow In farr.Rows Output0Buffer.AddRow() Output0Buffer.regon = regon Output0Buffer.rok = rok Output0Buffer.colName = r.Item("cName") Try j = Convert.ToDecimal(r.Item("cValue")) If j = 0 Then j = Nothing End If Catch ex As Exception j = Nothing End Try Output0Buffer.colValue = j 'r.Item("cValue") Output0Buffer.qtr = DateAdd("d", -1, DateSerial(rok, r.Item("qtr") * 3 + 1, 1)) Next farr.Clear() End Sub End Class