SSIS - Email Load Summary [ Insert, update counts] From SSIS Package

Scenario:

You are creating an SSIS Package that is loading multiple tables from different sources. You want to include logic in your SSIS Package to send Summary Email that should include records inserted, records updated in different tables.

Solution: 

To capture the count of inserts , updates at different points in SSIS Package, we will be using variables. The variables can be used in Data Flow Task with combination of Row Count Transformation to save the count of records which are loaded from Source to Destination, Variable can also get the record count from Execute SQL Task for inserts and updates. It depend upon your requirement which transformations you are using in your SSIS Package.

In our demo scenario, I am loading some records from SQL Source and loading into SQL Destination table. I will  only capture insert count and use in Load Summary email. You can capture information for multiples tables and use those variables in Summary Email.

Step 1:
Let's create below variables in your SSIS Package

EnvironemntName : 
This variable will be populated for the environment (Server Name) in which you are going to run your SSIS Package.

MailProfile:
Provide the Profile name which will be used for msdb.dbo.sp_send_dbmail stored procedure.

Recipients:
Provide the list of Recipients by separating them ";"

RowCnt:
Create Integer type variable that we will use to store number of rows inserted.
Fig 1: Create Variables in SSIS Package for Load Summary Email

Step 2:
Use RowCnt variable to capture row count from Source to Destination. I am loading records from SQL Source. I have used Multicast just for the test purpose to land records. You will be using your Destination that can be SQL Server Table, Flat File, Excel, Oracle etc.
Fig 2: Using RowCnt Variable in Data Flow Task To Get Number of Rows Inserted

Step 3:
Now we have record count saved in RowCnt variable, we can use this in our Load Summary.Bring the Execute SQL Task and connect to your Data Flow Task as shown below and then configure.
Fig 3: Connect Load Summary Task at the end of Tasks in Control Flow Pane

Double click on the Execute SQL Task and configure as shown below
Fig 4: Build Expressions for Load Summary Email in Execute SQL Task

I have used below expressions, You can take these expressions and add more variables and table names as per your requirements.

"EXEC msdb.dbo.sp_send_dbmail
 @profile_name = '"+ @[User::MailProfile] +"', 
@recipients = '"+ @[User::Recipients] +"',
 @body = '"+"Hi All,

Total of "+(DT_STR,50,1252) @[User::RowCnt] +" Records are loaded in [dbo].[Customer] table successfully.

Thank you',"
+"@subject = '"+ @[System::PackageName]  + " Executed successfully in "+ @[User::EnvironemntName]+"'"


Step 4:
Once we will execute this demo SSIS Package, we will receive email as shown below.
Fig 5: Load Summary Email from SSIS Package





2 comments:

  1. I feel SQL,SSIS and other such aspects usually help one provide more and more aspects of finding the best solutions to complex IT problems.

    SSIS Postgresql Read

    ReplyDelete
  2. I tasted different delphi oracle components that connect to Oracle db, and chose the one with better features for me.

    ReplyDelete