In this step by step SSIS Tutorial I will show you how to use SSIS Row Count Transformation. For the purpose of this tutorial I will use SSIS 2012. There are a few main changes comparing to previous version of SSIS But the principle staying the same.
VIDEO: This is text based tutorial for video tutorial see Video SSIS Row Count Transformation
Other Versions of SQL Server:
- SQL Server 2012
- This blog post has been written using SQL Server 2012
- SQL Server 2008 / R2
- The examples we used should work in SQL Server 2008 and R2
- SQL Server 2005
- We suspect that this will work in SQL Server 2005 as well
The Row Count Transformation allows us to count rows that passed though the transformation and record it in a variable.
So I have got Data Flow Task with connection to AdventureWorksDW2012 .
I add new variable clicking the icon on the top right corner of Control Flow. I name which is 'RowCount' as a 'Integer' Data Type and Default Value '0'.So now I have got variable where I can stored resault.
My OLE DB Source have only 2 columns from One table.
I will go to Row Count and drag it from toolbox to Data Flow. I will create new data path from OLE DB Source to Row Count. And I duble click on Row Count and i get Row Count window in a Variable drop down box I select my variable RowCount.
I go to Control Flow to set up Breakpoint. I click on Data Flow Task I select Edit Breakpoints from the list.
I select two options so I will stop before and after it will go to variable.
I run the package.
Iclick Locals and expand the Varables
So I go to Variable RowCount and I 'Add Watch'.
Here is our Row Count Variable it seys Zero.
I will go inside the Data Flow and run it
I click on Data Flow Task
and we can see breathy that the number of rows is 60,398 So If we go to watch to se the varable you can se that the value had changed to the same number I click play to finished so that have.
I hope that will help