How to Map Multiple Columns to single column in SSIS

Question

I am new to SSIS and have this below flat file and need to load into Orders table in MS SQL Server,

ID,Order1,Package1,Order2,Package2
1,O1,P1,O2,P2
2,O3,P3,O4,P4
3,O5,P5,O6,P6

I need to load above flat file values into Orders table just like bleow using SSIS,

ID  Order   Package
1   O1       P1
2   O2       P2
3   O3       P3
4   O4       P4
5   O5       P5
6   O6       P6

and I am trying to map all (O1 to O6 and P1 to P6) columns to Order and Package as show below,but i could not.

any solution for this.

Answer 1

From SSIS solution point of view you could achieve this like below -

Once you have read your flat file you need to distribute your input row into two which could be done by Multicast. Then you have Union your multicast- ed data as one. Now in SSIS there is no straight forward concept of ROW_NUMBER() so that has to be achieved using Script Component as transformation.

Union All -

Script Component code (use this inside ScriptMain : UserComponent) -

int rowNumber = 1;
    string order = "";
    string package = "";

public override void Input0_ProcessInputRow(Input0Buffer Row)
{
    if ((order != Row.Order) & (package != Row.Package))
    {
        Row.ID = rowNumber;
        rowNumber = rowNumber + 1;
    }

    order = Row.Order;
    package = Row.Package;
}

Reference : link

Answer 2

You need to unpivot the data.

This could be done in the source query

SELECT [ID], [Order1] AS [Order], Package1 AS [Package] FROM <table_name>
UNION ALL
SELECT [ID], [Order2] AS [Order], Package2 AS [Package] FROM <table_name>

Or in SSIS a Multi Cast followed by a Union All transform should work the same.

[Source]---->[Multi Cast]====>[Union All]---->[Destination]

The setup for the Union All would look like

Output  | Input 1  | Input 2
-----------------------------
ID      | ID       | ID
Order   | Order1   | Order2
Package | Package1 | Package2

In your example you create a new ID, this could be done with ROW_NUMBER or in SSIS using a Script Transform

Example using ROW_NUMBER

SELECT 
   [ID] = ROW_NUMBER() OVER(ORDER BY [ID], [Priority]),
   [Order],
   [Package] 
FROM (
    SELECT [ID], 1 AS [Priority], [Order1] AS [Order], [Package1] AS [Package] 
    FROM <table_name>
    UNION ALL
    SELECT [ID], 2 AS [Priority], [Order2] AS [Order], [Package2] AS [Package] 
    FROM <table_name>
) t