SSIS - how to split rows (not columns)

Question

I have a file with 100 rows and 20 columns, the first 2 rows are text and 98 others are numbers. There are no headers - I need to combine row1 and row2 to have the header.

I want to split row 1 to one output and row 2 to another output then combine col1 for row1 and row2, combine col2 for row1 and row2, combine col3 for row1 and row2 etc for all 20 columns to create new headers. After combining, I will use union all to write to a new output file that will now have 99 rows: row 1 is text with the new headers and the remaining 99 are numbers.

Does SSIS allow me to do that? I know it can do conditional split for column values but is there a way to split values based on the row or row number? can conditional split work with rows?

thx

How do you want to combine the two rows for headers? concatenate them? Do the values in the first two rows change every time? — Nick.McDermaid
Do you have a column which can identify Row1 and Row2 as two separate rows? — Himanshu gaur
Is there some reason it isn't as simple as adding a rownumber column so you can use it in a conditional split? — Tab Alleman
The likely answer is no, there's nothing really native that will allow you to do this (unless you show some sample data which could disprove my assumptions). The best approach I've found for situations like this is to set my Flat File Connection Manager to skip the first 2 rows and then have no headers. In a Script Task prior to the Data Flow, I'd parse the first two rows into SSIS scoped variables (or write to the output file). The data flow destination would be set to append and not overwrite so it'd only write the actual data (numbers) — billinkc
Thanks for all your answers. Yes, I was thinking of concatenating the two header rows, the header rows are consistent and will not change. I don't have a Row identifier but I can add code to create a row number column. Is it as simple as adding a rownumber and then just using conditional split? That would be great — James Rodriguez

Unknown Unknown · Accepted Answer · 2015-09-01T06:43:35

I can think of two solutions.

You create a Script Component of type "Source" for reading the file, that way you can control the row outputs, as you are reading over them. This removes the use/benefits of the connection manager/flat file component, so I would not recommend it as the first choice.
You create a short Script Component of type Transformation, and just alter the first few rows (assuming the reading is the proper line order).

For #2, this is how it would look (you can filter out the RowIndex 0): Within the script component make sure the following is setup (creating an Output Column 'RowIndex' and having the Input Columns marked as 'ReadWrite'):

After that is setup, the script just needs to be something like this:

public class ScriptMain : UserComponent
{
    public static int RowCount = 0;

    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        if (RowCount == 0)
        {
            // Store your row's information
            string Column0 = Row.Column0;
            string Column1 = Row.Column1;

            // Set the index (0), and make it fetch the next row
            Row.RowIndex = RowCount++;
            Row.NextRow();

            // With the next row (the second), combine the columns.
            Row.Column0 = Column0 + " " + Row.Column0;
            Row.Column1 = Column1 + " " + Row.Column1;
        }
        // Increment and store for each following row.
        Row.RowIndex = RowCount++;
    }
}

Hopefully that helps.

SSIS - how to split rows (not columns)

1 Answers