Row count

Question

HI Everybody,

I got flat file ,I want to transfer flat file data to tables as well as add one column to table which show record count .We have to use row count transformation for this ,I define variable and used in row count but I am unable to retrieve record count from variable if you guys have any idea please let me know.

Thanks in advance

Answer 1

I guess that a derived column transformation could help you to accomplish that.

Derived Column Transformation

AMB

Answer 2

Mike, you can't use the Row Count transform for this. While it does store the row count to a variable, it stores it internally while the data flow is running, and only writes it to the variable at the end of the data flow (during PostExecute). THis is a performance optimization, because it is expensive to access a variable for every row in the data flow. Unfortunately, it means the row count is not available to you durin the data flow.

My typical workaround to this is a script component, where I maintain a class scoped variable that holds the current row count. In the ProcessInputRow method, I increment the variable, and output it to a new column in the pipeline, so that I can leverage it later.

---

John Welch | www.varigence.com | www.agilebi.com | ssisUnit.codeplex.com

Answer 3

Thanks for your reply .After row count I used derived column and assign variable  to new count column but in table I am getting 0 ,it is not giving exact count .

If you know how to write script for this in script component please provide that.

Thanks in advance

Answer 4

public class ScriptMain : UserComponent
{
    private int rowCount = 0;

    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)
    {
        rowCount++;
        Row.RowCount = rowCount;
    }

}

Note that you need to add an output column named RowCount on the Inputs and Outputs page in the Script Transformation Editor.

Also, this will provide you with a running count. If you want to have the total count on each row, well, that's a different problem. :)

---

John Welch | www.varigence.com | www.agilebi.com | ssisUnit.codeplex.com

Answer 5

Thanks for providing me the code .

Script component in data flow is completely new for me .I do not know how to configure that .Do we need to use row count transformation in this .

 

Answer 6

No, you don't need the Row Count transform.

---

John Welch | www.varigence.com | www.agilebi.com | ssisUnit.codeplex.com