CODEWITHSUNDEEP
ssisetl
Mr Nothing
Mr Nothing

Reputation: 1

How to Parse the Text file in SSIS

Iam new to SSIS , Iam facing the below issue while parsing a text file which contains the below sample data

Below is the requirement

-> Need to Capture the number after IH1(454756567) and insert into one column as InvoiceNumber -> Need to insert the data between ABCD1234 to ABCD2345 into another column as TotalRecord .

Many thanks for the help .

ABCD1234

IH1 454756567 686575634

IP2 HJKY TXRT

IBG 23455GHK

ABCD2345

IH1 689343256 686575634

IP2 HJKY TXRT

IBG 23455GHK

ABCD5678

Upvotes: 0

Views: 415

Answers (2)

KeithL
KeithL

Reputation: 5594

This is the script component to process the entire file. You need to create your output and they are currently being processed as strings.

This assumes your file format is consistent. If you don't have 2 columns in IH1 and IP2 ALL the time. I would recommend a for loop from 1 to len -1 to process. And send the records to their own output.

public string recordID = String.Empty;
public override void CreateNewOutputRows()
{
    string filePath = ""; //put your filepath here
    using (System.IO.StreamReader sr = new System.IO.StreamReader(filePath))
    {
        while (!sr.EndOfStream)
        {
            string line = sr.ReadLine();
            if (line.Substring(0, 4) == "ABCD") //Anything that identifies the start of a new record
                                                // line.Split(' ').Length == 1 also meets your criteria.
            { 
                recordID = line;
                Output0Buffer.AddRow();
                Output0Buffer.RecordID = line;
            }

            string[] cols = line.Split(' ');

            switch (cols[0])
            {
                case "IH1":
                    Output0Buffer.InvoiceNumber = cols[1];
                    Output0Buffer.WhatEverTheSecondColumnIs = cols[2];
                    break;
                case "IP2":
                    Output0Buffer.ThisRow = cols[1];
                    Output0Buffer.ThisRow2 = cols[2];
                    break;
                case "IBG":
                    Output0Buffer.Whatever = cols[1];
                    break;
            }
        }
    }
}

Upvotes: 2

Tab Alleman
Tab Alleman

Reputation: 31795

You'll need to do this in a script component.

Upvotes: 1

Related Questions