CODEWITHSUNDEEP
sql-serverssisetlforeach-loop-containerdataflowtask
venugopal
venugopal

Reputation: 89

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

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

Upvotes: 3

Views: 2550

Answers (2)

Hadi
Hadi

Reputation: 37313

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

Upvotes: 3

userfl89
userfl89

Reputation: 4790

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;

Upvotes: 1

Related Questions