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

jdweng · Accepted Answer · 2020-08-10 18:13:39Z

0

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] });
                }
            }
        }
    }
}

answered Aug 10, 2020 at 18:13

jdweng

34.5k3 gold badges17 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

rentatta Over a year ago

jdweng, thank You for answer. This solution is really helpful, but my input table is not static - this was just an example of how should the transformation look like... that is why I am using the approach described in the link - blog. I must use the Component metadata to import the input columns and I would like to use the MapInputColumn method to do this transformation... Please, could You rewrite your solution using this method? Or something from the solution mentioned in the blog? Thank You very much

jdweng Over a year ago

Why? Once the data is in a DataTable you can get the names from the table. A default mapping is automatically created when you use a SQL Data Adapter.

rentatta Over a year ago

Yes, You are right.. so I create a Datatable as You wrote, but how can I assign to this Datatable data from my input, when I have only

InstanceSource.SetComponentProperty("TableName", this.SAPTableName); InstanceSource.SetComponentProperty("InternalXML", this.InternalXML);

this component of Data flow and set the connection between the source and destination

IDTSPath100 path = dataFlowMainPipe.PathCollection.New(); path.AttachPathAndPropagateNotifications(DataSource.OutputCollection[0], OLEDBDestination.InputCollection[0]);

I don't know ho to use this information to fill DataTable

jdweng Over a year ago

Try following : stackoverflow.com/questions/25922107/…

Collectives™ on Stack Overflow

SSIS Script Component Transformation of Input Programatically

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related