SSIS Row Count Transformation

The Row Count might be the simplest one of all the transformations in Data Flow Task. It just counts the rows of the data which pass through the transform in a data flow and save the number in a variable. The transform has one input and one output without any error output.

We prepared the source Excel file C:\SSIS\Books.xlsx with its contents below. The total number of records is 7.

Just follow the steps below to see how to create and use the row count transformation.

1. Copy and paste the package DataConversion.dtsx in project LearnSSIS2 and rename the copy to RowCount.dtsx.


2. Open the package and go to Data Flow Task design panel by clicking Data Flow tab at the top. Then drag & drop Row Count from Toolbox into the panel.



The layout looks ugly. Let's adjust it.



3. Use your mouse to select all the transforms or just click CTRL-A, then click the menu FORMAT -> Align -> Centers, and then click the menu FORMAT -> Vertical Spacing -> Decrease several times.



Now the layout looks good.



4. Create an Int32 variable count.

If you don't know how to create it, please check the Variable section.



5. Right-click the "Row Count" and select "Edit...". Then choose the variable "User::count" in the drop-down list.





6. Click OK to get over the setting.

To verify the result, we'll use a Send Mail Task to get the count of the inserted records.



7. Click Control Flow tab and add a Send Mail Task and replace the MessageSource with the following in the Expressions of the editor.


Select

"There are " + (DT_WSTR, 20) @[User::count] + " records added to the database."



For detail settings, please check Send Mail Task section.



8. Run the package and you will get an email indicating 7 records added to the database.