RowCount Transformation in SSIS

RowCount Transformation :  The RowCount transformation provides a way to track how many rows are flowing through a particular path. You can use this information in logs as diagnostic information or for sending in an email as a notification of the work a package has completed.

Counts the rows flowing through the data flow and writes the final count in a variable. Here we are gonna discuss an example on the same.

In the Control Flow tab, add a variable named “RowCnt.” Ensure that the variable is package-scoped and of type Int32 .

Add a dataflow task and create a connection manager that connects to the AdventureWorksDW database. Add an OLE DB Data Source to the Data Flow design surface. Configure the source to point to your AdventureWorksDW database’s connection manager.

Double Click on the OLE DB Component and Select  SQL Command to write customized sql.

Add a Row Count Transform to the Data Flow.

Open the Advanced Editor and select the variable named “User::RowCnt” as the Variable Name property. Your editor should resemble.

When you edit Row Count Transformation you could see the Input Columns  and Input and Output Properties Tabs  along with Component Properties. Please ignore both of them.

RowCount Transformation has been configured. Now Return to the Control Flow tab and add a Script Task. This task is not really going to perform any action. Instead it will be used to show the conditional ability to perform steps based on the value returned by the Row Count Transform.

Connect the Data Flow Task to the Script Task. Double click on Script Task and  Set up the ReadOnlyVariable as User::RowCnt which was created as package variable and then Edit the script to write  the message box popup code.

Write the piece of script code to show the count value in alert message box.

Now the package is ready to be executed. Go to the Solution Explorer and right click on the package and select “Execute Package”. If all components turn “GREEN”, it means package has run successfully, if there is any error, the component which has failed to execute will be shown in “RED” Color.

Package executed successfully and shows the rows count as in message box. This is it guys !!