Row Count Transformation [Audit Information]

Row Count Transformation [Audit Information]

Scenario:

Let’s say 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

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

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

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

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

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.