SSIS 2008 Row Count Transformation - Row count return 0

This should be rather simple but I don't know why I get Row Count as Zero when I use ROW COUNT transformation in Data Flow Task. I have created a variable(NoOfRecords) with Package scope.

Variable name set to variable NoOfRecords in Row Count Transformation.

Used a Derived column to assign the row co开发者_运维知识库unt.

The package runs successfully and shows record count 265

But the Derived column shows record count as 0 instead of 265 rows.

---

After the Row Count, add an Aggregate Taks and select count option in the Operation tab in the Aggregate task properties. Then you can use the row count variable for further operation where it holds the total row count of the input file.

---

Row Count is processed after rows has passed.

You're adding the variable to each row as they pass through the Derived Column step, but at this time, the variable has not been updated (as it happens after all rows has passed) - so the value 0 is correct.

You -might- be able to achieve this by using an asynchronous task before your derived (but i'm not sure this'll work, it just popped to my mind). Add a Sort or Aggregate step before your Derived and try again.

---

I used this in the query as an efficient way of getting the row count:

count(all SnapshotDate) over () as nRowCount

---

Here's the successful technique for recording rows that worked in my situation. The scenario is I want to log the rows migrated between tables. The RowCount doesn't get populated until you exit the DataFlow. [Control Flow] 1. Data Flow Task a. read origin data - Source control b. Add RowCount transformation. Link a to b. Right-click RowCount and map to UserVariable (int64) c. Add Destination control for loading table. d. Link b to c. 2. Add Execute SQL Task to ControlFlow. right click, edit INSERT SQL statement: Insert Into LogTable(rowcount) Values(?) Parameter Mapping Variable Direction DataType ParameterName ParameterSize User::RowCount INPUT LONG 0 -1