ssis package email

Select query to be assigned to a job.

Using SMTP process as a windows service then use the Job Package to send a mail of the resultant value.

Here is one possible option. Following example might give you an idea of how you can achieve emailing a result set using Send Email task. This example shows how to loop through the query result set to form the message body that will then be emailed using Send Email task.

If you don't want to send e-mail when the result set is blank, you can add an Expression to the precedence constraint between Loop resultset and Send email tasks.

The example uses SSIS 2008 R2 and SQL Server 2008 R2 database.

Step-by-step process:

Create a table named dbo.EmailData using the script provided under SQL Scripts section.
Screenshot #1 shows sample data that Execute SQL task will query and send it in an e-mail in this example.
On the SSIS package, create 5 variables as shown in screenshot #2.
On the SSIS package, place the following tasks: Execute SQL task, Foreach loop container, Script task within the Foreach loop container and Send Email task.
Configure the Execute SQL task as shown in screenshots #3 and #4.
Configure the Foreach loop container as shown in screenshots #5 and #6. Variable mappings section shows the order in which the query result columns appear and how they are assigned to SSIS variables. These variables will be used to form the email message inside the Script task.
In the Script task, replace the code with the one shown under the Script task code section. The script task has very simple plain text email message formatting.
Configure the Send Email task as shown in screenshot #7. You need to configure it with valid email address in From and To fields.
After configuring the Control flow tasks, your package should look like as shown in screenshot #8.
Sample package execution is shown in screenshot #9.
E-mail sent by the package is shown in screenshot #10. Some information have been removed from the screenshot. You can compare the table data shown in screenshot #1 with this email output and they should same.

Reference code:

http://stackoverflow.com/questions/6439663/how-to-send-the-records-from-a-table-in-an-e-mail-body-using-ssis-package

Mark as answer, if helpful.

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Bhuvanesh Mohankumar10y ago
Select query to be assigned to a job.
Using SMTP process as a windows service then use the Job Package to send a mail of the resultant value.
Here is one possible option. Following example might give you an idea of how you can achieve emailing a result set using Send Email task. This example shows how to loop through the query result set to form the message body that will then be emailed using Send Email task.
If you don't want to send e-mail when the result set is blank, you can add an Expression to the precedence constraint between Loop resultset and Send email tasks.
The example uses SSIS 2008 R2 and SQL Server 2008 R2 database.
Step-by-step process:
Create a table named dbo.EmailData using the script provided under SQL Scripts section.
Screenshot #1 shows sample data that Execute SQL task will query and send it in an e-mail in this example.
On the SSIS package, create 5 variables as shown in screenshot #2.
On the SSIS package, place the following tasks: Execute SQL task, Foreach loop container, Script task within the Foreach loop container and Send Email task.
Configure the Execute SQL task as shown in screenshots #3 and #4.
Configure the Foreach loop container as shown in screenshots #5 and #6. Variable mappings section shows the order in which the query result columns appear and how they are assigned to SSIS variables. These variables will be used to form the email message inside the Script task.
In the Script task, replace the code with the one shown under the Script task code section. The script task has very simple plain text email message formatting.
Configure the Send Email task as shown in screenshot #7. You need to configure it with valid email address in From and To fields.
After configuring the Control flow tasks, your package should look like as shown in screenshot #8.
Sample package execution is shown in screenshot #9.
E-mail sent by the package is shown in screenshot #10. Some information have been removed from the screenshot. You can compare the table data shown in screenshot #1 with this email output and they should same.
Reference code:
http://stackoverflow.com/questions/6439663/how-to-send-the-records-from-a-table-in-an-e-mail-body-using-ssis-package
Mark as answer, if helpful.
0