SSIS Splitting lines in flat file using conditional splitting or script
I am new to SSIS, your idea or solution is very welcome.
I have a flat file with the first line as files (not a header). The second line is the actual data.
Data Description
Format of the first line = porter_name, date, number of records in the file for example:
^ 06022017 Vendor name ^ 3
ID1 ^ Member1 ^ NEW YORK ^ 050117 ^ 50.00 ^ GENERAL ^ ANC ID2 ^ member2 ^ FLORIDA ^ 050517 ^ 50.00 ^ MOBILE ^ ANC ID3 ^ member3 ^ SEATL ^ 050517 ^ 80.00 ^ MOBILE ^ ANC
EOF
Problem
Using SSIS, I want to split the first line to output1 and the second line further to output2.
With the conditional split, I thought I could do it. But I'm not sure what condition to give to split the lines. Should I work with multicast?
thank
source to share
Thanks everyone. Here is an alternative solution
I used a script component in SSIS for this.
Step1: Create a variable named RowNumber.
Step 2: Then add a script component that will add an extra column and increment the line 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;
}
Step 3: Use the output of the script component as input to the conditional split and create a condition with RowNumber == 1.
Multicast will split the data appropriately.
source to share
First I have to make sure you have the correct number of columns in your flat file connection: Edit the flat file connection -> Advanced tab, click the New button to add columns. In your example, you should have 7, column 0 - column 6.
Now add conditional separation and arguments of two cases:
Output Name Condition
HeaderRow [Column 0] == "Supplier_Name"
DetailRow [Column 0] != "Supplier_Name"
Now direct them to output and output 2
source to share
Extending the answer to the Allerman tab.
For our project, we used a shell script component internally Execute process task
that runs a simple power shell command to grab the first line of the file.
See the MSDN blog on how to run a shell script shell.
Power shell script to get first line
Get-Content C:\foo\yourfolderpath\yourfilename.txt -First 1
This note only helps if you have one, but in general it helps to avoid handling large files (GB and above) with the wrong header. This simple power wrapper runs in milliseconds, rather than most processes / scripts that would require loading a full file into memory, slowing things down.
source to share