SSIS: Logging total number of rows inserted in multiple tables.

Suppose you are inserting multiple rows in different staging tables in a single package. And you have to log total number of rows inserted into a logging table for audit purpose. How do we do it.

Let's see how we can accomplish this inside a ssis package.

 Say we are loading data in 3 tables.

 Table A,
 Table B,
 and Table C.

 Let's create a variable "varSrcRowCountAL" int data type.

 Add a SQL Execute task and set Result Set to "Single Row". In SQL Statement Add following sql statement

 Select ( (Select count(*) From TableA) + (Select count(*) From TableB) +  (Select count(*) From [TableC));

 In Result Set tab of SQL Execute task, Add variable like this
 Result Name = 0
 Variable Name = User::varSrcRowCountAL

  This will save total row count in our variable.
 
  Now add another SQL Execute Task to truncate TableA, TableB and TableC
 
  Add a sequence container, add three data flow task to load your data to 3 three table.
 
  Outside your sequence container, add another SQL Execute Task.
 
  This time, define a variable called "varDestRowCountAL" int datatype. In SQL Statement Add following sql statement.
  Select ( (Select count(*) From TableA) + (Select count(*) From TableB) +  (Select count(*) From [TableC));
 
  This value we will save in our destination variable varDestRowCountAL. Do the same as you did to count to your source count.

Now we can use this variable to update our logging table.