SSIS Process records between header and trailer in flat file

Question

I have a SSIS package that uses a conditional split to get any batch header or trailer in a line delimited flat file, take the first line, and grab an error code from it. If the error code is > 0, I write out all of the normal records in between the batch header and trailer to a report with that error code. Otherwise I write out only normal records with errors codes. Here's what that example looks like:

//No batch level error
00000BH
00123NRNormalRecordData
00000NRNormalRecordDataNoError
00000BT

Which would look like:

╔═══════════╦══════════════════╗
║   Error   ║   Record Data    ║
╠═══════════╬══════════════════╣
║       123 ║ NormalRecordData ║
╚═══════════╩══════════════════╝

And:

//Batch level error
05555BH
00000NRNormalRecordData
00000NRNormalRecordData
00000BT

Which would look like:

╔═══════╦═════════════════════════╗
║ Error ║       Record Data       ║
╠═══════╬═════════════════════════╣
║  5555 ║ NormalRecordData        ║
║       ║                         ║
║  5555 ║ NormalRecordData        ║
╚═══════╩═════════════════════════╝

My problem is with multiple batches this now screws up (There use to only ever be one batch). now I want to do something like the following

//Multi batch
00000BH
00123NRNormalRecordError
00000NRNormalRecord
00000BT
00000BH
00000SRSecondaryRecordType //want to ignore batches with no NR normal records
00000BT
05555BH
00000NRNormalRecord
00000NRNormalRecord
00000BT

Due to saving the batch level error into a variable and checking if it's null when I write the records out this report would incorrectly look like:

╔═══════╦═════════════════════╗
║ Error ║    Record Data      ║
╠═══════╬═════════════════════╣
║  5555 ║ NormalRecordError   ║
║  5555 ║ SecondaryRecordType ║
║  5555 ║ NormalRecord        ║
║  5555 ║ NormalRecord        ║
║  5555 ║ NormalRecord        ║
╚═══════╩═════════════════════╝

When I would want it to look like:

╔═══════╦═══════════════════╗
║ Error ║    Record Data    ║
╠═══════╬═══════════════════╣
║  123  ║ NormalRecordError ║
║  5555 ║ NormalRecord      ║
║  5555 ║ NormalRecord      ║
╚═══════╩═══════════════════╝

This is because the logic looks a little something like:

• store batch level error get all normal records with an error, unless
• batch level error > 0 then get them all write all rows with batch
• level error if there was one or write only normal rows that error (this case would work as intended, because the batch level variable is not populated)

My first thought was a conditional split. However, this would only let me do a condition at the row level, where as I need context of rows that have come prior.

How would you tackle this?

Answer 1

You could use a script component transformation to parse the column and add rows based on your conditions. The header error can be stored in a variable that is declared outside the Input0_ProcessInputRow method. Here are the steps that I used:

1. I used your data above to make a single column flatfile with the column name data
2. Add a script component as transformation
3. Check Data as a input column
4. Add new Output called RecordOutput
5. Add columns to out put: error as int, RecordData as string

The code:

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{

    int error;

    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        //gets the row type

        string rowType = Row.Data.Substring(5, 2);

        //only sets the error variable if it is a header record
        if (rowType == "BH")
        {
            error = Convert.ToInt32(Row.Data.Substring(0, 5));
        }

        //Only adds a record for NR rows
        if (rowType == "NR" && (error > 0 || Convert.ToInt32(Row.Data.Substring(0, 5)) > 0))
        {
            RecordOutputBuffer.AddRow();
            if (error > 0)
            {
                RecordOutputBuffer.Error = error;
            }
            else
            {
                RecordOutputBuffer.Error = Convert.ToInt32(Row.Data.Substring(0, 5));
            }
            RecordOutputBuffer.RecordData = Row.Data.Substring(7, Row.Data.Length - 7);
        }
    }

}

Here is what the component looks like:

Here are the results: