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  

• Create Load Summary Email logic in SSIS Package
• How to Configure Send Mail Task and Send Email in SSIS Package
• Create a file with Date From SQL Server Table and Send as Attachment in SSIS Package 
• Attach Multiple Files Dynamically in Send Mail Task in SSIS Package
• Send Error Email with Error Code, Error Description by using Send Mail Task in SSIS Package 
• Send List of Files from A Folder as Email by using Send Mail Task
• Use Send Mail Task with Precedence Constraints in SSIS Package
• Read EmailTo,EmailFrom,Subject and Body From Text File and Send Emails by using Send Mail Task in SSIS Package
• Send Load Summary Email by using Send Mail Task in SSIS Package