SSIS Row Count Transformation to Log Inserted Rows

This SSIS Integration Services article shows how to use the row count transformation to log the total number of inserted records or rows with an example.

The below screenshot shows the records in the Employee text file.

Employee File

SSIS Row Count Transformation to Log Inserted Rows

Drag and drop the Data Flow Task into the control flow region. Next, add a variable to hold the count of inserted rows.

Add Data Flow task and variables

Double-click to open the SSIS Data Flow Region. Then, add the flat file source and double-click on it to open the Source Editor.

Click the New button to open the Flat File Connection Manager Editor window. Next, click the Browse button to choose the Employee file.
If the file’s first row has header names, checkmark the Column Names in the first data row option.

Flat File Connection to Choose text file

Use the advanced section to change the Data type. Here, the column data type must match the destination table; otherwise, it throws an error. After you finish, click OK to close the window.

advanced section to change the Data type

Add Row Count Transformation to the SSIS Data Flow Task and connect it to Flat File Source to log inserted rows. Then, double-click the Row count to choose the variable.

SSIS Row Count Transformation to Log Inserted Rows

Next, drag the OLE DB Destination and double-click on it to open the Editor. Next, choose the OLE DB Connection Manager.

Select the table from the list. If not, click the new button to create a new one.

OLEDB Destination to configure OLE DB Connection Manager

Next, go to the Mappings tab to check the input and available destination column mapping.

check the input and available destination column mapping

Add Execute SQL Task to the Control Flow Region.

Add Execute SQL Task in SSIS Row Count Transformation to Log Inserted Rows

Double-click to open the editor and choose the existing connection. Next, change the source type to Direct and write the below-shown insert statement.

INSERT INTO [dbo].[Package Information]
([Machine Name]
,[Package Name]
,[User Name]
,[Start Time]
,[Inserted Rows])
VALUES (?, ?, ?, ?, ?)
INSERT Into Statement

Within the Parameter mapping, assign variables to all the columns specified insert statement. Here, we use the system and user (row count) variables.

Parameter Mapping

Remember to change the data type because they have to match the destination table; otherwise, it won’t insert. For example, under the Parameter Name, 0 represents the first column, 1 for the second column, and so on.

SSIS Row Count Transformation to Log Inserted Rows

Run the SSIS Row Count Transformation to Log Inserted Rows package.

Run the SSIS Row Count Transformation to Log Inserted Rows package

Open the SQL Management Studio to see the result.

View Result

The below SQL query shows the log information.

Run the SSIS Row Count Transformation to Log Inserted Rows package