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):
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.
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.*/
public class ScriptMain : UserComponent
private string LastValue = "";
public override void PreExecute()
Add your code here for preprocessing or remove if not needed
public override void 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;
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:
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.