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.
- Copy and paste the package DataConversion.dtsx in project LearnSSIS2 and rename the copy to RowCount.dtsx.
- 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.
- 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.
- Create an Int32 variable count.
- Right-click the "Row Count" and select "Edit...". Then choose the variable "User::count" in the drop-down list.
- Click OK to get over the setting.
- Click Control Flow tab and add a Send Mail Task and replace the MessageSource with the following in the Expressions of the editor.
- Run the package and you will get an email indicating 7 records added to the database.
The layout looks ugly. Let's adjust it.
Now the layout looks good.
If you don't know how to create it, please check the Variable section.
To verify the result, we'll use a Send Mail Task to get the count of the inserted records.
"There are " + (DT_WSTR, 20) @[User::count] + " records added to the database."
For detail settings, please check Send Mail Task section.