Row Count of various files with in a For Each Loop Container

Question

I have a Row Count transformation in Data Flow Task and this Data Flow Task is in a For Each Loop Container. So when I am running the package I'm getting the row count for only one file but not all the files.Can anyone help me with this

Do you want a row count for _each_ file, or a total of all of the row counts for _all_ of the files? — Eric Brandt, Mar 08 '19 at 18:27
You can use an expression task for this purpose i think it is more recommended then using a scrip task since you are just incrementing a variable you don't have any sophisticated logic to implement — Yahfoufi, Mar 11 '19 at 11:18

score 3 · Answer 1 · answered Mar 08 '19 at 23:37

Using an Expression Task

The Expression Task creates and evaluates expressions that set variable values at runtime, using the Expression Builder

Add 2 variables to your ssis package:

@[User::TotalRowCount]: To store the total count
@[User::RowCount]: To be used in the Row Count transformation

Add an Expression Task inside the Foreach Loop container with the following expression:

@[User::TotalRowCount] = @[User::TotalRowCount] + @[User::RowCount]

References

score 1 · Answer 2 · answered Mar 08 '19 at 14:30

You can use a Script Task to do this. Start off by creating another SSIS variable which will store the total count for all iterations of the Foreach Loop. After this place a Script Task after the Data Flow Task inside the loop. Add the variable holding the row count for each iteration as a ReadOnlyVariable and the variable that will store the total count for all iterations in the ReadWriteVariables field. The following example uses C# to update the value of the total row count variable (TotalCount) by adding the count of rows from the variable holding this for the current iteration (CurrentInterationCount) to it.

int currentRows = Convert.ToInt32(Dts.Variables["User::CurrentInterationCount"].Value.ToString());
int totalRows = Convert.ToInt32(Dts.Variables["User::TotalCount"].Value.ToString());

Dts.Variables["User::TotalCount"].Value = currentRows + totalRows;

Row Count of various files with in a For Each Loop Container

2 Answers2

Using an Expression Task