How to include header row in SSIS Script Component Row Count?

Question

My goal is to return a line count of header row and all data rows. This line count is to be stored in a variable and used by a Script Task to write the value to a text file.

I have tried programming the following Script Component:

public class ScriptMain : UserComponent
{
  private int rowNumber;
  public override void PreExecute()
  {
    base.PreExecute();
    rowNumber = 0;
  }

  public override void PostExecute()
  {
    base.PostExecute();
    Variables.MORTLRowCount = rowNumber;
  }

  public override void Input0_ProcessInputRow(Input0Buffer Row)
  {
    rowNumber++;
    Row.totalRows = rowNumber;
  }

}

The script component uses the PostExecute method to store the rowNumber integer to a SSIS variable. I've placed the script component after the Flat File Source transformation and before a Flat File Destination.

The Script Task creates a separate file, control file, and requires the total number of rows including header from the script component. The Script task is programmed as follows:

        public void Main()
        {
        int feedVersion = 200;
        string filename = 
        (string)Dts.Variables["User::MortLCurrentFilename"].Value.ToString();
        string ctlfilename = (string)Dts.Variables["User::MORTLCtlDestinationFileName"].Value.ToString() ;
        int recordCount = (int)Dts.Variables["User::MORTLRowCount"].Value ; 
        string firstline = "Filename | Feed Version | Record Count";
        string secondline = filename + "|" + feedVersion + "|" + recordCount;
        string filetext = firstline + System.Environment.NewLine + secondline;
        System.IO.File.WriteAllText(ctlfilename, filetext);

        Dts.TaskResult = (int)ScriptResults.Success;
        }

The Script task produces the file as needed. However, the record count produced by the Script Task is 1 less than the actual record count of the file. The file, with headers, has 132 rows and the Script Task reports 131 records.

Is there a way to ensure accurate reporting of all rows (both header and data) by the Script Task and Component?

score 1 · Accepted Answer · answered Apr 24 '23 at 23:33

Yes, no?

You have an off-by-one situation here, no? You initialize to zero which is fine but somewhere, you need to account for the row(s) that comprise your header.

Either rework the initialization to

rowNumber = 1;

or account for it in the postExecute with

// +1 for our header
Variables.MORTLRowCount = rowNumber +1;

If you continue to be worried about the row count, don't mind burning IO cycles, and don't mind potentially repeating some logic (not sure if you have to deal with embedded line endings - guessing not since this smells like a mainframe extract), then drop the Script Component within the Data Flow. Add a Script Task immediately after the Data Flow and the sole purpose of this is to crack open the newly created file and count the number of lines. Something like Determine the number of lines within a text file and assign the value back to your MORTLRowCount variable.

And then use your existing Script Task to write the control file.

How to include header row in SSIS Script Component Row Count?

1 Answers1