This is the third and final part to this thread on how to capture the number of rows being processed in SSIS to a flat file.

In Part I we learned how not to do it and why it didn’t work.

In Part II we showed how to use the ROW COUNT transformation to populate an SSIS variable, use the DERIVED COLUMN transformation to convert the value of the variable to a column of data, and write the row count to a flat file.  We learned that we needed to use two data flows to do this.  The biggest benefit of this approach is that we can use the same technique of using the DERIVED COLUMN transform to write any SSIS variable out to a flat file, such as an audit file.

In this post we will show a simpler way to capture the row count of a transaction.  The downside is that it will not work for any variable in the package, as in the case of the DERIVED COLUMN.

So let’s get to work…

1.  We’ll only need a single data flow for this solution.  And here’s what it looks like:

What’s new here is the use of the AGGREGATE transformation circled in red.  Basically all this transform is doing is the classic T-SQL COUNT(*).

2.  To configure the AGGREGATE TRANSFORM just select the (*) input column.

3.  The last step is to map the output of the AGGREGATE to the input of theFLAT FILE DESTINATION transform, or any other target transform you need.

The following execution of the plan shows how the AGGREGATE transform takes the 940 rows and outputs the singe row count row that we were looking for.

CONCLUSION:  SSIS provides a powerful set of options when doing just about any task.  That certainly holds true even when doing something as simple as capturing the number of rows in a data flow.  To capture the row count all you really need to do is use the AGGREGATE transform.  If you’re more interested in being able to write any SSIS variable out than use the DERIVED COLUMN transform.