Knight's Microsoft SQL Server 2012 Integration Services 24-Hour Trainer

Chapter 25

Auditing Data with the Row Count Transform

Often in an ETL process you may be required to create an auditing table that records how many rows were loaded. SSIS has made this easy to accomplish with the Row Count Transform.

This transform has the ability to count rows in a Data Flow and record that count for later use in conjunction with an Execute SQL Task. The count must be placed into a variable, which can then be used in the Control Flow for inserting into an audit table.

If you have used this transform in previous versions of SSIS, you will notice that it has been simplified even more in SQL Server 2012. To configure the Row Count Transform, connect it to any point in the Data Flow that you want to record the number of rows. Double-click the transform to open the Row Count Editor. In the Variable property, specify what variable (package and project parameters cannot be used here) will store the row count that the transform records.

Another valuable way to use the Row Count Transform is as a destination to send your data to. Because you don’t physically have to commit stream data to a table to retrieve the count, it can act as a destination, terminating your data stream and enabling you to view the Data Flow’s data with a data viewer.

Try It

In this Try It, your company needs you to create a package that runs only if the ErrorLog table in the AdventureWorks2012 database contains any rows. After this lesson, you’ll know how to insert a row count into a variable and ...