SSIS – Retain Values from Previous Rows

There are times when data is read in a fashion that needs to be examined and compared based on the value(s) of the previous record(s) for a particular grouping.  For example, one might have the following data which does not have the month filled out for all columns (Some might say that this data is poorly formatted, and I would agree, but we have to bring it in anyway):

image

The way that you can accomplish this would be through using a script component transformation in the data flow.  Make sure to set the column you are interacting with to ReadWrite usage type in the input columns tab.

image

Next, flip to the script component and then edit script.  The following is written in C#, although it could just as easily been accomplished in VB.

/* Microsoft SQL Server Integration Services Script Component
*  Write scripts using Microsoft Visual C# 2008.
*  ScriptMain is the entry point class of the script.*/

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
{

    private string LastValue = "";

    public override void PreExecute()
    {
        base.PreExecute();
        /*
          Add your code here for preprocessing or remove if not needed
        */
    }

    public override void PostExecute()
    {
        base.PostExecute();
        /*
          Add your code here for postprocessing or remove if not needed
          You can set read/write variables here, for example:
          Variables.MyIntVar = 100
        */
    }

    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        if (Row.Month_IsNull == true)
        {
            Row.Month = LastValue;
        }
        else
        {
            LastValue = Row.Month;
        }

    }

}

Fill in the appropriate source, counts, destinations and error files and give it a go.  The following is the output of the example run:

image

The thing to remember here is that we do not just use the code above for only this type of application.  It can be used for any number of commands where we care about the values from past row(s) – Running totals, point in time maximum values, etc.  The only thing to be careful of, is that data examined based on previous rows will need to be sorted properly to ensure that the correct data is retained.

Advertisements
This entry was posted in SSIS and tagged , . Bookmark the permalink.

2 Responses to SSIS – Retain Values from Previous Rows

  1. queasyfish says:

    Thank you. I used this to process an EFW2 (W2) file which has employer information on one row and employee information on another, each with different fixed-width columns. I appended the (previous row’s) employer ID to the following row’s employee information and outputted (output?) back to a flat file. Then extracted the two row types into two tables with separate flat file connection managers. Now I can join the data as normal.

  2. amit says:

    thanks it has helped me a lot.. and saved my time.
    I have used this….
    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
    /*
    Add your code here
    */
    {

    if (Row.Columnname == “”)
    {
    Row.Columnname = LastValue;
    }
    else
    {
    LastValue = Row.Columnname ;
    }

    }

    }

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s