SSIS Script Component Transformation of Input Programatically

Question

I am trying to use SSIS script component to transform my input data using also the approach described in this blog: https://blog.theobald-software.com/2010/09/20/building-ssis-package-with-xtract-is-table-programmatically/

Everything works fine, I have created the source and destination components, but I don't know how to use the mapping part of the code to tranform my input columns to desired format (described below)

//map the columns
IDTSPath100 path = dataFlowMainPipe.PathCollection.New();
path.AttachPathAndPropagateNotifications(DataSource.OutputCollection[0], OLEDBDestination.InputCollection[0]);
 
IDTSInput100 input = OLEDBDestination.InputCollection[0];
IDTSVirtualInput100 vInput = input.GetVirtualInput();
 
foreach (IDTSVirtualInputColumn100 vColumn in vInput.VirtualInputColumnCollection)
{
IDTSInputColumn100 vCol = InstanceDestination.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READWRITE);
InstanceDestination.MapInputColumn(input.ID, vCol.ID, input.ExternalMetadataColumnCollection[vColumn.Name].ID);

There is a 1:1 mapping from input to output in the code, but I NEED TO MAP first 1..n-1 columns from input to only 4 columns in output with multiplying the number of rows to (n-1)*input.CountRows, see the example below

INPUT

Al  _1  _2  _3  _4  _5  _6  Value
a   A   5a  4a  2oa 5oa 4oa 10
b   B   5b  4b  2ob 5ob 4ob 20
c   C   5c  4c  2oc 5oc 4oc 30
d   D   5d  4d  2od 5od 4od 40
e   E   5e  4e  2oe 5oe 4oe 50
f   F   5f  4f  2of 5of 4of 60

OUTPUT

N   P   Key Value
Al  _1  a   A
Al  _1  b   B
Al  _1  c   C
Al  _1  d   D
Al  _1  e   E
Al  _1  f   F
Al  _2  a   5a
Al  _2  b   5b
Al  _2  c   5c
Al  _2  d   5d
Al  _2  e   5e
Al  _2  f   5f
Al  _3  a   4a
Al  …   …   …

I am using Script component as Source and this whole code is executed in PreExecute phase.

Thank You very very much for any advice BR R

Answer 1

You need to unpivot the table

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace ConsoleApplication165
{
    class Program
    {
        static void Main(string[] args)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("A1", typeof(string));
            dt.Columns.Add("_1", typeof(string));
            dt.Columns.Add("_2", typeof(string));
            dt.Columns.Add("_3", typeof(string));
            dt.Columns.Add("_4", typeof(string));
            dt.Columns.Add("_5", typeof(string));
            dt.Columns.Add("_6", typeof(string));
            dt.Columns.Add("Value", typeof(string));

            dt.Rows.Add(new object[] {"a","A", "5a", "4a", "2oa", "5oa", "4oa", "10"});
            dt.Rows.Add(new object[] {"b","B", "5b", "4b", "2ob", "5ob", "4ob", "20"});
            dt.Rows.Add(new object[] {"c","C", "5c", "4c", "2oc", "5oc", "4oc", "30"});
            dt.Rows.Add(new object[] {"d","D", "5d", "4d", "2od", "5od", "4od", "40"});
            dt.Rows.Add(new object[] {"e","E", "5e", "4e", "2oe", "5oe", "4oe", "50"});
            dt.Rows.Add(new object[] {"f","F", "5f", "4f", "2of", "5of", "4of", "60"});

            DataTable dt1 = new DataTable();
            dt1.Columns.Add("N", typeof(string));
            dt1.Columns.Add("P", typeof(string));
            dt1.Columns.Add("Key", typeof(string));
            dt1.Columns.Add("Value", typeof(string));

            string[] headers = dt.Columns.Cast<DataColumn>().Select(x => x.ColumnName).ToArray();

            for (int col = 1; col < headers.Length; col++)
            {
                foreach (DataRow row in dt.AsEnumerable())
                {
                    dt1.Rows.Add(new object[] { "A1", headers[col], row[0], row[col] });
                }
            }
        }
    }
}