CODEWITHSUNDEEP
c#.netsql-serverssisetl
shockwave
shockwave

Reputation: 3272

SSIS Split rows in a flatfile using conditional split or script

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

Upvotes: 3

Views: 2672

Answers (4)

shockwave
shockwave

Reputation: 3272

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.

enter image description here

Upvotes: 1

DhruvJoshi
DhruvJoshi

Reputation: 17126

Expanding on Tab Allerman's answer.

For our project, we used a power shell script component inside an Execute process task which runs a simple power shell command to grab the first line of the file.

See this MSDN blog on how to run power shell script.

Power shell script to get the first line

Get-Content C:\foo\yourfolderpath\yourfilename.txt -First 1

This note only helps in case like yours, but generically helps in avoiding processing large files (in GBs and upwards) which have incorrect header. This simple power shell executes in milliseconds as opposed to most of the processes/scripts which will require to load a full file into memory, slowing things down.

Upvotes: 0

Steve Ford
Steve Ford

Reputation: 7753

I would first make sure that you have the correct number of columns in your Flat File Connection: Edit the Flat File Connection -> Advanced Tab press the New button to add columns. In your example you should have 7, Column 0 to Column 6.

Now add a conditional split and ass two case statements:

Output Name      Condition
HeaderRow        [Column 0] == "Supplier_Name"
DetailRow        [Column 0] != "Supplier_Name"

Now route these to the Output 1 and Output 2

Upvotes: 0

Tab Alleman
Tab Alleman

Reputation: 31785

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.

Upvotes: 3

Related Questions