SSIS Split rows in a flatfile using conditional split or script

Question

I'm new to SSIS, your idea or solution is greatly appreciated.

I have a flat file with the first row as the file details(not the header). The second row onwards is the actual data.

Data description

First-row format= Supplier_name, Date, number of records in the file eg:

Supplier_name^06022017^3
ID1^Member1^NEW YORK^050117^50.00^GENERAL^ANC ID2^Member2^FLORIDA^050517^50.00^MOBILE^ANC ID3^Member3^SEATTLE^050517^80.00^MOBILE^ANC
EOF

Problem

Using SSIS I want to split the First row into output1 and second row onwards into output2.

With the help of conditional split, I thought I can do this. But I'm not sure what condition to give in order to split the rows. Should I try with multicast?

Thanks

Answer 1

I would handle this by using a script task (BEFORE the dataflow) to read the first row and do whatever you want with it.

Then in the dataflow task, I would set the flat file source to ignore the first row and import the second row on as data.

Answer 2

Thank you all. Here is an alternative solution

I used a script component in SSIS to do this.

Step1: Create a variable called RowNumber.

Step2: Then add a script component which will add an additional column and increments row numbers.

SSIS Script component

 private int m_rowNumber;
public override void PreExecute()
{
    base.PreExecute();
    m_rowNumber = 0;
}

public override void Input0_ProcessInputRow(Input0Buffer Row)
{
    m_rowNumber++;
    Row.RowNumber = m_rowNumber;
}

Step3: Use the output of Script component as the input of conditional split and create a condition with RowNumber == 1.

The Multicast will split the data accordingly.