SSIS- How to Use Row Count Transformation (Audit Information) in SSIS Package

Scenario:

Let’s say that we receive flat file from our client that we need to load into our SQL Server table. Beside loading the data from flat file into our destination table we also want to track how many records loaded from flat file. To keep track of Records Inserted we can create Audit table.

Solution:

As we have to keep track for number of records loaded, we need to create a table where we can insert this information. Let’s create a table with three columns

CREATE TABLE dbo.PkgAudit
  (
     PkgAuditID      INT IDENTITY(1, 1),
     PackageName     VARCHAR(100),
     LoadTime        DATETIME DEFAULT Getdate(),
     NumberofRecords INT
  ) 

Step 1: 

Create Connection Manager for your flat file. I used below records in flat file

CountryName,SalePersonName,SaleAmount

uSA,aamir shahzad,100

Italy,andy,200

UsA,Mike,500

brazil,Sara,1000

INdia,Neha,200

Brazil,Barbra,200

Mexico,Anthony,500

Step 2: 

Create SSIS variable called RecordsInserted as shown below

Create variable in SSIS Package to capture Inserted Row Count 

Step 3:

Place Row Count Transformation to Data Flow Pane and connect Flat File Source to Row Count Transformation. Double click on Row Count Transformation and choose RecordsInserted Variable as shown below

Save the Row Count in variable by Row Count Transformation in SSIS Package

Step 4:

Use any destination such as OLE DB Destination, Flat File where you want to insert data from Source. In our case I used Multicast for testing purpose as can be seen below

Use Multicast as Test Destination to see the Record Count in SSIS Package

Step 4:

When we execute our package the rows are inserted into destination by passing Row Count Transformation. All the count is saved in the variable. Our next goal is to save this information to our Audit Table for record.

In Control Flow Pane , Bring Execute SQL Task and Configure as shown below

How to insert variable value to SQL Server Table by using Execute SQL Task in SSIS Package

Map the User Variable (RecordsInserted) and System Variable( PackageName) to Insert statement as shown below

How to map parameters in Execute SQL Task to variables in SSIS Package for Insert Query

Final Output

Let's run our package and see if information is recorded in our Audit table. As you can see below 7 records were loading from source file to our destination. The same Audit table can be enhanced by adding more columns such as records update, record deleted , records rejected and save all these stats while execution of package in different variables and at the end insert into Audit Table.

Get Insert Row Count in SSIS Package and Log to Table 

Video Demo : How to get Record Count in SSIS Package and Save in SQL Server Table