How to use Row Count Transformation in SSIS

Description: -

The Row Count Transformation in SSIS is used to counts the rows as they pass through a data flow and stores the final count in a variable. For example, if we want to inform the Manager to inform the number of rows this package is inserted then we can use Row Count Transformation for counting the rows and then use Script Task to send an e-mail message to the Manager.

In SQL Server Integration Services, Row Count Transformation will not the update the variable value until the last row of the source data has passed through the Row Count Transformation. So, we can’t use the updated values in the middle. Let us design one package so that we can understand this transformation better.

STEP 1: Drag and drop the data flow task from the toolbox to control flow and rename it as Row Count Transformation DFT.

STEP 2: This transformation stores counted rows information in variable only, so before using this transformation we need to create a variable on the Variables tab and add an integer variable to the package, as shown in below Figure.

Right click on the Control Flow region will open the context menu with multiple options. Select the Variables option from the context menu to create a variable.

In this case, we’ve chosen to add a variable named INR_Cur and USD_Cur of integer type and assigned value to 0.

STEP 3: Drag and drop OLE DB Source from toolbox to data flow region and configure it will server. Double click on OLE DB source in the data flow region will open the connection manager settings and provides space to write our SQL statement.

STEP 5: Click on columns tab to verify the columns. In this tab we can uncheck the unwanted columns also.

STEP 6: Drag and drop Conditional Split transformation in Data flow. After add the condition for the currency column, so we can have spilt customer who has INR and USD currency.

STEP 6: Drag and drop row count transformation for count the INR and USD currency customer in the customer table. Here I have configure two row count transformation for get the INR and USD currency customer.

When you configure INR row count transformation then will ask for the Input Output selection from the Conditional Split transformation. Select the INR Currency for the INR Row count.

Double click on the Row Count Transformation will open small Row Count Window to select the User defined Variable from the drop down. Here we are selecting the already created variable (INR_Cur).

Or you can configure the Row Count Transformation with more traditional approach. Right click on the Row Count Transformation will open the context menu with multiple options. Here select the Show Advanced Editor option from the context menu as shown in below screenshot.

Same configure the USD row count transformation.

STEP 7: Drag and drop two Flat file destination from the toolbox and configure it text file. So we can INR and USD customer list in the flat file. Here I have configured flat file destination like below image.