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.
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.
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. Checkmark the Column Names in the first data row option if the file’s first row has header names.
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.
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.
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.
Next, go to the Mappings tab to check the input and available destination column mapping.
Add Execute SQL Task to the Control Flow Region.
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 (?, ?, ?, ?, ?)
Within the Parameter mapping, assign variables to all the columns specified insert statement. Here, we use the system and user (row count) variables.
Remember to change the data type because they have to match the destination table; otherwise, it won’t be inserted. For example, under the Parameter Name, 0 represents the first column, 1 for the second column, and so on.
Run the SSIS Row Count Transformation to Log Inserted Rows package.
Open the SQL Management Studio to see the result.
The below SQL query shows the log information.
