SSIS Script Component Transformation: Split record into multiple records (ver. dynamic columns)

'... Output has to be created manually (there's no solution for that) and column mappings 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 reg 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 = 0 '= Nothing Dim id As New DataColumn("qtr", GetType(Integer)) ds.Tables.Add(farr) farr.Columns.Add(id) farr.Rows.Add(1) farr.Rows.Add(2) farr.Rows.Add(3) farr.Rows.Add(4) farr.PrimaryKey = New DataColumn() {id} 'If Not farr.Columns.Contains(column.Name) Then ' farr.Columns.Add(colName) ' MessageBox.Show("Dodana kolumna:" + colName) 'End If For Each column In Me.ComponentMetaData.InputCollection(0).InputColumnCollection() colName = column.Name.ToString() columnValue = rowType.GetProperty(column.Name) If colName = "rok" Then rok = columnValue.GetValue(Row, Nothing) ElseIf colName = "regon" Then reg = columnValue.GetValue(Row, Nothing) Else i = 0 Dim dt As DataTable = ds.Tables.Add(colName) Dim id1 As New DataColumn("qtr", GetType(Integer)) dt.Columns.Add(id1) 'dt.PrimaryKey = New DataColumn() {id1} Dim var As New DataColumn(colName, GetType(String)) dt.Columns.Add(var) arr = columnValue.GetValue(Row, Nothing).ToString.Split(";") For Each s In arr i = i + 1 'Try ' j = Convert.ToDecimal(s) 'Catch 'ex As Exception ' j = 0 'End Try 'With farr.Columns(colName) dt.Rows.Add(i, s) 'End With Next ReDim arr(0) farr.Columns.Add(colName, GetType(String)) 'MessageBox.Show("nazwa kol " + colName) 'Dim rel As DataRelation = New DataRelation(colName, farr.Columns("qtr"), dt.Columns("qtr")) 'ds.Relations.Add(rel) For Each chi As DataRow In dt.Rows For Each par As DataRow In farr.Rows If par("qtr") = chi("qtr") Then 'MessageBox.Show(colName + " col0: " + dt.Columns(0).ColumnName + " q: " + chi(0).ToString() + " val: " + chi(1).ToString()) par.Item(colName) = chi(colName) 'farr.Rows.Add(pr) 'MessageBox.Show(colName) End If Next Next dt.Clear() End If Next 'For Each dtt As DataTable In ds.Tables ' farr.Merge(dtt) 'Next For Each r As DataRow In farr.Rows 'MessageBox.Show("Next column") Output0Buffer.AddRow() Output0Buffer.regon = reg Output0Buffer.rok = rok Output0Buffer.nnk = r.Item("nnk") Output0Buffer.nno = r.Item("nno") Output0Buffer.nnp = r.Item("nnp") Output0Buffer.nnw = r.Item("nnw") Output0Buffer.nbk = r.Item("nbk") Output0Buffer.nbo = r.Item("nbo") Output0Buffer.nbp = r.Item("nbp") Output0Buffer.nbw = r.Item("nbw") Output0Buffer.nnp = r.Item("nnp") Output0Buffer.nnw = r.Item("nnw") Output0Buffer.pnk = r.Item("pnk") Output0Buffer.pno = r.Item("pno") Output0Buffer.pnp = r.Item("pnp") Output0Buffer.pnw = r.Item("pnw") Output0Buffer.zw1 = r.Item("zw1") Output0Buffer.zw2 = r.Item("zw2") Output0Buffer.zw3 = r.Item("zw3") Output0Buffer.zw4 = r.Item("zw4") Output0Buffer.zw5 = r.Item("zw5") Output0Buffer.zp1 = r.Item("zp1") Output0Buffer.zp2 = r.Item("zp2") Output0Buffer.zp3 = r.Item("zp3") Output0Buffer.zp4 = r.Item("zp4") Output0Buffer.zp5 = r.Item("zp5") Output0Buffer.wzbk = r.Item("wzbk") Output0Buffer.wzbr = r.Item("wzbr") Output0Buffer.wzbz = r.Item("wzbz") Output0Buffer.wznk = r.Item("wznk") Output0Buffer.wznr = r.Item("wznr") Output0Buffer.wznz = r.Item("wznz") Output0Buffer.qtr = DateAdd("d", -1, DateSerial(rok, r.Item("qtr") * 3 + 1, 1)) Next farr.Clear() End Sub End Class