Extract Multiple Flat File Lines as One Using C# in SSIS Script Component

Question

Within the Script of the Script Component in SSIS, I am trying to create one record from a flat file as shown here:

Starting Data:

HEADER|INVOICE_1|INVOICE_DATE|VENDOR_ID|
DETAIL|SKU|DESCRIPTION|AMOUNT|
DETAIL|SKU|DESCRIPTION|AMOUNT|
HEADER|INVOICE_2|INVOICE_DATE|VENDOR_ID|
DETAIL|SKU|DESCRIPTION|AMOUNT|
HEADER|INVOICE_3|INVOICE_DATE|VENDOR_ID|
DETAIL|SKU|DESCRIPTION|AMOUNT|

End Result:

HEADER|INVOICE_1|INVOICE_DATE|VENDOR_ID|SKU|DESCRIPTION|AMOUNT|
HEADER|INVOICE_1|INVOICE_DATE|VENDOR_ID|SKU|DESCRIPTION|AMOUNT|
HEADER|INVOICE_2|INVOICE_DATE|VENDOR_ID|SKU|DESCRIPTION|AMOUNT|
HEADER|INVOICE_3|INVOICE_DATE|VENDOR_ID|SKU|DESCRIPTION|AMOUNT|

The End Result will be mapped to an OLE DB destination within SSIS.

I've originally wrote the script to use StreamReader to pull each line type (Header, Detail) as group outputs but quickly realized that there was no identifier to join them back together.

This is what I currently have written:

System.IO.StreamReader reader = new System.IO.StreamReader(this.Variables.varFileName);

        while (!reader.EndOfStream)
        {

            string line = reader.ReadLine();

            string[] items = line.Split('|');

            // HEADER = 000

            if (items[0] == "000")
            {
                HeaderBuffer.AddRow();
                HeaderBuffer.H1 = items[0];
                HeaderBuffer.H2 = items[1];
                HeaderBuffer.H3 = items[2];
                HeaderBuffer.H4 = items[3];
                HeaderBuffer.H5 = items[4];
                HeaderBuffer.H6 = items[5];
                HeaderBuffer.H7 = items[6];
                HeaderBuffer.H8 = items[7];
                HeaderBuffer.H9 = items[8];
                HeaderBuffer.H10 = items[9];
                HeaderBuffer.H11 = items[10];
                HeaderBuffer.H12 = items[11];
                HeaderBuffer.H13 = items[12];
            }

            // DETAIL = 001

            else if (items[0] == "001")
            {
                DetailBuffer.AddRow();
                DetailBuffer.D1 = items[0];
                DetailBuffer.D2 = items[1];
                DetailBuffer.D3 = items[2];
                DetailBuffer.D4 = items[3];
                DetailBuffer.D5 = items[4];
                DetailBuffer.D6 = items[5];
            }
        }

Thank you in advance for all of your help! If you've any questions, please let me know.

Hadi · Accepted Answer · 2018-02-15T20:37:32.890

There is no need to create two output buffer to achieve this, if the line contains

HEADER|INVOICE_1|INVOICE_DATE|VENDOR_ID|

Then store the value of these fields in variables, then for each line that contains

DETAIL|SKU|DESCRIPTION|AMOUNT|

Create an output row and use the values from the variables + the values of this row.

Next time a HEADER|INVOICE_1|INVOICE_DATE|VENDOR_ID| occur, just clear the variable values and assign new values to them. And repeat the procedure.

System.IO.StreamReader reader = new System.IO.StreamReader(this.Variables.varFileName);

            string H1;
            string H2;
            string H3;
            string H4;
            string H5;
            string H6;
            string H7;
            string H8;
            string H9;
            string H10;
            string H11;
            string H12;
            string H13;

        while (!reader.EndOfStream)
        {

            string line = reader.ReadLine();

            string[] items = line.Split('|');

            // HEADER = 000


            if (items[0] == "000")
            {

                H1 = items[0];
                H2 = items[1];
                H3 = items[2];
                H4 = items[3];
                H5 = items[4];
                H6 = items[5];
                H7 = items[6];
                H8 = items[7];
                H9 = items[8];
                H10 = items[9];
                H11 = items[10];
                H12 = items[11];
                H13 = items[12];
            }

            // DETAIL = 001

            else if (items[0] == "001")
            {
                DetailBuffer.AddRow();
                DetailBuffer.D1 = items[0];
                DetailBuffer.D2 = items[1];
                DetailBuffer.D3 = items[2];
                DetailBuffer.D4 = items[3];
                DetailBuffer.D5 = items[4];
                DetailBuffer.D6 = items[5];
                DetailBuffer.H1 = H1;
                DetailBuffer.H2 = H1;
                DetailBuffer.H3 = H2;
                DetailBuffer.H4 = H3;
                DetailBuffer.H5 = H4;
                DetailBuffer.H6 = H5;
                DetailBuffer.H7 = H6;
                DetailBuffer.H8 = H7;
                DetailBuffer.H9 = H8;
                DetailBuffer.H10 = H9;
                DetailBuffer.H11 = H10;
                DetailBuffer.H12 = H11;
                DetailBuffer.H13 = H12;             


            }
        }

That logic makes perfect sense. How would I assign the variables, then write it to reset them each time a new 001 line is read? Thank you in advance... haven't used C# in 7+ years and was intermediate at best. — Adam, Feb 15 '18 at 20:06
Awesome! I went in and added H1-H13 to the Detail Output in the Script Component and what you wrote looks good, however, in the DetailBuffer portion it is saying, "Use of unassigned local variable 'H1'" and so on for each H1-H13 variable. — Adam, Feb 15 '18 at 20:30
Ah, got it... Went in and assigned each variable to null initially. Thank you again for your help! — Adam, Feb 15 '18 at 20:49

score 1 · Answer 2 · answered Feb 16 '18 at 15:12

@Hadi's answer hits your request perfectly. however, architectually it would be better to have a header and detail tables. I combined your approaches and you were very close in the start.

System.IO.StreamReader reader = new System.IO.StreamReader(this.Variables.varFileName);

    while (!reader.EndOfStream)
    {

        string line = reader.ReadLine();
        **string Hkey; //Make this whatever the key is**
        string[] items = line.Split('|');

        // HEADER = 000

        if (items[0] == "000")
        {
            HeaderBuffer.AddRow();
            HeaderBuffer.H1 = items[0];
            HeaderBuffer.H2 = items[1];
            HeaderBuffer.H3 = items[2];
            HeaderBuffer.H4 = items[3];
            HeaderBuffer.H5 = items[4];
            HeaderBuffer.H6 = items[5];
            HeaderBuffer.H7 = items[6];
            HeaderBuffer.H8 = items[7];
            HeaderBuffer.H9 = items[8];
            HeaderBuffer.H10 = items[9];
            HeaderBuffer.H11 = items[10];
            HeaderBuffer.H12 = items[11];
            HeaderBuffer.H13 = items[12];
            **Hkey = items[keynode]**
        }

        // DETAIL = 001

        else if (items[0] == "001")
        {
            DetailBuffer.AddRow();
            **DetailBuffer.Hkey = Hkey;**
            DetailBuffer.D1 = items[0];
            DetailBuffer.D2 = items[1];
            DetailBuffer.D3 = items[2];
            DetailBuffer.D4 = items[3];
            DetailBuffer.D5 = items[4];
            DetailBuffer.D6 = items[5];
        }
    }

I would also parse the items into the correct data types in the code — KeithL, Feb 16 '18 at 15:33

Extract Multiple Flat File Lines as One Using C# in SSIS Script Component

2 Answers2