Read Subject,Reciptient and Body from SQL Server Table and Send Email by Using Send Mail Task - SQL Server Integration Services(SSIS) Tutorial

Scenario:

Let's say we have a table which store Email To , Email From , Subject and Body of emails those we need to send on daily basis. We want to design an SSIS Package that runs on schedule and send email for each of the record that is stored in the Table.

Solution:

To solve above scenario, we will be using following components of an SSIS Package
  1. Read all the records from a table and stored in Object type variable by using Execute SQL Task
  2. Loop through Object type variable by using For-each Loop Container and store values in variables
  3. Write Expressions in Send Mail Task
CREATE TABLE dbo.Email (
    id INT identity(1, 1)
    ,EmailFrom VARCHAR(100)
    ,EmailTo VARCHAR(100)
    ,Subject VARCHAR(100)
    ,Body NVARCHAR(MAX)
    )
GO

INSERT INTO dbo.Email (
    Emailfrom
    ,EmailTo
    ,Subject
    ,Body
    )
VALUES (
    'aamirsqlage@gmail.com'
    ,'techbrotherssqlage@gmail.com'
    ,'Test Subject1'
    ,'

Hi

This is email for Test1

Thank you

Tech Brothers'
    )

INSERT INTO dbo.Email (
    Emailfrom
    ,EmailTo
    ,Subject
    ,Body
    )
VALUES (
    'aamirsqlage@gmail.com'
    ,'techbrotherssqlage@gmail.com'
    ,'Test Subject2'
    ,'

Hi

This is email for Test2

Thank you

Tech Brothers'
    )

INSERT INTO dbo.Email (
    Emailfrom
    ,EmailTo
    ,Subject
    ,Body
    )
VALUES (
    'aamirwarraich2001@gmail.com'
    ,'techbrotherssqlage@gmail.com'
    ,'Test Subject3'
    ,'

Hi

This is email for Test3

Thank you

Tech Brothers'
    )

SELECT EmailFrom
    ,EmailTo
    ,Subject
    ,Body
FROM dbo.Email




Read Email information from SQL Server Table and Send Email by using Send Mail Task in SSIS Package - SSIS Tutorial



  Related Posts/Videos on Send Mail Task