How to Pivot a group of columns as rows in output

Question

i am trying to convert a file with layout as follows:

id1 | id2 | id3 |...| id34 | A | B | C | A | B | C | A | B | C | 100 group of A B C|
a1  | b1  | c1  |...| aa1  | 3 | 7 | 5 | 7 | 5 | 3 | 1 | 1 | 1 | ...               |
a2  | b2  | c2  |...| aa2  | 4 | 3 | 7 | 0 | 4 | 8 | 9 | 8 | 4 | ...               |

Output should be:

id1 | id2 | id3 |...| id34 | A | B | C |
a1  | b1  | c1  |...| aa1  | 3 | 7 | 5 |
a1  | b1  | c1  |...| aa1  | 7 | 5 | 3 |
a1  | b1  | c1  |...| aa1  | 1 | 1 | 1 |
a2  | b2  | c2  |...| aa1  | 4 | 3 | 7 |
a2  | b2  | c2  |...| aa1  | 0 | 4 | 8 |
a2  | b2  | c2  |...| aa1  | 9 | 8 | 4 |

i'm currently doing it through Script transformation Component but its very slow.

i am not very familiar with the Pivot and Unpivot Component

Is there any option which can set the group of rows | A | B | C | and SSIS will consider it as one set and convert it in my desired format?

THANKS IN ADVANCE!!

Answer 1

I would first load the file "as is" into a wide SQL table. I would number the columns groups e.g.

id1 | id2 | id3 |...| id34 | A | B | C | A | B | C | A | B | C | ...| a1 | b1 | c1 |...| aa1 | 3 | 7 | 5 | 7 | 5 | 3 | 1 | 1 | 1 | ... |

... loads into columns ...

id1 | id2 | id3 |...| id34 | A1 | B1 | C1 | A2 | B2 | C2 | A3 | B3 | C3 | ... a1 | b1 | c1 |...| aa1 | 3 | 7 | 5 | 7 | 5 | 3 | 1 | 1 | 1 | ... |

Then I would create a Variable (String) to hold a dynamic SQL statment, driven by another Variable (Int). I would create a For Each Loop to increment the Int Variable from 1 to 100. Within the loop I would use the String Variable to drive a Data Source.

The first iteration, the SQL would look like:

SELECT id1, id2, id3 ... id34, A1 AS A, B1 AS B, C1 AS C FROM Staging_Table

2nd iteration, the SQL would look like this:

SELECT id1, id2, id3 ... id34, A2 AS A, B2 AS B, C2 AS C FROM Staging_Table

... and so on.

The output from each iteration would go via an OLE DB Destination into the target table.