Send Load Summary Email by using Send Mail Task in SSIS Package - SQL Server Integration Services(SSIS) Tutorial

Scenario:

Let's say we have to create an SSIS Package for Insert/Update Operation. We have to read the data from Flat File and then Insert or Update in SQL Server Table depending upon the value of ID column. Once the UPSERT operation is done, we want to send the Number of Records Inserts or Number of Records Update.


Solution

To solve this scenario we can use following parts of an SSIS Package

  1. Create SSIS Package Parameters to store Email To and Email From
  2. Use Data Flow Task to read the records from Flat file and Insert the New Records and Update the existing Records. 
  3. Use Lookup Transformation in Data Flow Task to Find out if Record already exists of not
  4. Create Variables InsertCount and UpdateCount to store Insert and Update Record Count
  5. Use Variables in Expressions in Send Mail Task to Build Load Summary Email



Expressions
"Hi Team,
Package has completed successfully, please check the load summary below
Total Records Inserted : "+(DT_WSTR,10) @[User::InsertCount]+"
Total Records Updated : "+(DT_WSTR,10)  @[User::UpdateCount]+"

Please contact Tech Brothers in case of Questions.
Thank you
Tech Brother"






How to Send Load Summary Email by using Send Mail Task in SSIS Package - SSIS Tutorial



  Related Posts/Videos on Send Mail Task