Knight's 24-Hour Trainer: Microsoft® SQL Server® 2008 Integration Services

Chapter 21. 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.

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 Advanced Editor for Row Count. In the VariableName property you specify what variable 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 allowing you to view the Data Flow's data with a data viewer.

Try It

In this lesson, your company needs you to create a package that runs only if there are any rows in the ErrorLog table in the AdventureWorks2008 database. After this lesson, you'll know how to insert a row count into a variable and use it dynamically in your package.

Lesson Requirements

Create a new package named Lesson21 and make the following changes. You can also find the completed Lesson21.dtsx package ...