The Row Count transformation counts rows as they pass through a data flow and stores the final count in a variable.
Row Count Transformation does not update the variable value until the last row of the source data has passed through this SSIS Transformation.
Lets see, how to implement row count transformation in SSIS.
Lets open the SQL Server Data Tool (SSDT) and create a new SSIS package.
Here we create a SSIS package in existing solution, If you do not have project already created then you can create new SSIS project solution otherwise can add new SSIS package to an existing solution as shown below.
Once you select New SSIS package, a new package with default name as Package1 is added inside SSIS packages folder.
Lets rename it as pkg_RowsCountTransformation.
Note: do not change or remove the package extension .dtsx.
Now go to SSIS toolbox and drag Data Flow Task into Control Flow container or view. The Data Flow task encapsulates the data flow engine that moves data between sources and destinations, and lets the user transform, clean, and modify data as it is moved.
While the Control Flow is used to build and modify the control flow in the package.
Now we will create a variable on the Variables tab and add an integer variable to the package.
Just right click on the control flow view, it opens the context menu.
Then select the Variables option from the context menu to create a variable.
Lets add a variable named CountRows of type integer and assigned value to 0 as shown below.
Now double click on Data Flow Task, it navigates you to Data flow container tab for selected Data Flow Task as shown below.
Data Flow is used to build and modify the data flow for selected data flow task.
As our source is text file as shown below.
lets got to Other Sources and drag a Flat file source into Data flow view.
Now we will assign a connection manager to the flat file source.
Just double click on flat file source or right click then select edit from context menu as shown below.
You will see a Flat File Source Editor window opens, now you can select an existing connection manager or can create a new by clicking on New button.
Lets click on New.. button,
Once you click on New.. button, a Flat file Connection Manager Editor window opens.
Here you can specify the source file properties, file format and so on..
In General tab, once you specified the flat file name, you can also select the row delimiter, skip any row and tick the checkbox, if flat file has column names in first row.
Now in Columns Tab, you can verify and modify row and column delimiter, also can see the data preview.
Next, in Advanced tab, you can check column data type, and size also can add new column, modify the details of an existing column, and delete them if required.
After that in a Preview tab, you can see the preview of source data.
If everything looks fine, just click on OK button.
Once you click on OK button, you get back to Flat file Source Editor window.
Now go to Columns Tab, you can select required output columns also can change the output column names.
After that in Error Output Tab, you can specified the behavior of the component in case of failure, you can specified any one option as follows:
- Ignore Failure: It ignores any failure while reading rows from source and the package will continue executing even any error occurred.
- Redirect Row: It redirects the failed rows to other component which is connected with the error precedence constraints.
- Fail component: It stops the execution of package in case of any failure.
We go with default selected value that is Fail component.
Now select and drag Row Count transformation into Data Flow view.
Now add path, connect Flat file source arrow to row count.
Next, double click on the Row Count Transformation, a pop up window open.
Just select the User-defined Variable from the drop-down. Here we select the variable CountRows.
Now go back to Control flow tab, and take a script task component and drag it into Control flow view.
Now to configure script task, just double click on script task component.
You will see, a Script Task Editor window opens.
Next, select the user-defined variable as a read-write variable.
Then click ok and after that click on Edit Script button to open class file.
Now add following code in Main method, script language that is used is c#.
String Message = Dts.Variables["User::CountRows"].Value.ToString(); MessageBox.Show(Message);
Now, close the class file, then click on OK button.
Lets execute the package, and you can see it display the message box with the total number of rows in file.
Click the ok button to stop the script.
Lets verify the records in source file, you can see there are 4 rows in file.
Also Read..
SSIS – Derived Column Transformation
SSIS – Conditional Split Transformations
SSIS – FTP task Send File to FTP Server
SSIS – FTP Task Send Multiple Files To FTP Server
SSIS – Aggregate Transformation Basic Mode
SSIS – Copy column Transformation
