Row Count Transformation In SSIS
RowCount:-The Row Count Transformation in SSIS is used to count the rows as they pass through a data flow and store the final count in a variable.
Procedure:-
INPUT:-
- Go to file manager and check the source file as shown below.
- we need to create package under integration services project5.
- Under solution explorer we need to rename the package as required. i.e
RowCount.dtsx.
- Click on the variables and create two variables filepath and RowCount and specify the data type and values.
- Drag and drop the Execute SQL task from the SSIS toolbox to the control flow.
- Double click on execute SQL task and specify the connection and select SQL statement to create the audit log table.
Audit Log Table:-An audit log table is a specialized database table designed to record and track events related to data changes within a system. These tables serve as a historical record, capturing information about various operations performed on data.
- In SQL statement enter the SQL query as given below to create the audit log table.
- Go to parameter mapping click on add to select the row count variable in variable name and parameter name to 0 as shown below and click on ok.
- Drag and drop data flow task from the SSIS tool box to control flow and connect it with the execute SQL task.
- Double click on data flow task and drag and drop flat file source from the SSIS tool box.
- Double click on the flat file source and in the connection manager specify the connection manager name and browse the file name from the file manager.
- Go to columns to check the columns in source file and click on preview to preview the data of source file.
- Click on the columns and check that the external column matches with the output column.
- Drag and drop the RowCount transformation from the SSIS tool box to the data flow task and connect it to the source.
- Double click on Row Count transformation and select the row count variable and click on ok.
- Drag and drop the OLEDB destination from the SSIS tool box to the data flow task and connect it to the row count transformation.
- Double click on the OLEDB destination and specify the OLEDB connection manager and click on new to create a target table.
- In the OLEDB destination click on the mappings and check that the input columns map with the destination column and click on ok.
- Drag and drop the Execute SQL task from the SSIS tool box to the control flow and connect it to the data flow task.
- Double click on the Execute SQL task1 and specify the connection and click on SQL statement.
- In the SQL statement enter SQL query to insert the values of audit_log_divya table by entering the SQL query and click on ok.
- Go to parameter mapping and click on add to add the variable row count as shown below and click on ok.
- Click on start to execute the package and check that the package gets executed.
- Go to SQL server database and check the output of the OLEDB destination.
- here flat file source data gets inserted into the table by giving the syntax we can see the output as shown below.
- here by using row count transformation we can get the no of records inserted in the audit log table as shown below.
