How to send the records from a table in an e-mail body using SSIS package?

ssis

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.

Hope that helps.

SQL Scripts: .

CREATE TABLE [dbo].[EmailData](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ItemId] [varchar](255) NOT NULL,
    [ItemName] [varchar](255) NOT NULL,
    [ItemType] [varchar](255) NOT NULL,
    [IsProcessed] [bit] NULL,
 CONSTRAINT [PK_EmailData] PRIMARY KEY CLUSTERED ([Id] ASC)) ON [PRIMARY]
GO

Script task code:

C# code that can be used only in SSIS 2008 and above. .

/*Microsoft SQL Server Integration Services Script Task
   Write scripts using Microsoft Visual C# 2008.
   The ScriptMain is the entry point class of the script.
*/

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;

namespace ST_7f59d09774914001b60a99a90809d5c5.csproj
{
    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {

        #region VSTA generated code
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

        public void Main()
        {
            Variables varCollection = null;
            string header = string.Empty;
            string message = string.Empty;

            Dts.VariableDispenser.LockForWrite("User::EmailMessage");
            Dts.VariableDispenser.LockForWrite("User::ItemId");
            Dts.VariableDispenser.LockForWrite("User::ItemName");
            Dts.VariableDispenser.LockForWrite("User::ItemType");
            Dts.VariableDispenser.GetVariables(ref varCollection);

            //Set the header message for the query result
            if (varCollection["User::EmailMessage"].Value == string.Empty)
            {
                header = "Execute SQL task output sent using Send Email Task in SSIS:\n\n";
                header += string.Format("{0}\t{1}\t\t\t{2}\n", "Item number", "Item name", "Item type");
                varCollection["User::EmailMessage"].Value = header;
            }

            //Format the query result with tab delimiters
            message = string.Format("{0}\t{1}\t{2}",
                                        varCollection["User::ItemId"].Value,
                                        varCollection["User::ItemName"].Value,
                                        varCollection["User::ItemType"].Value);

            varCollection["User::EmailMessage"].Value = varCollection["User::EmailMessage"].Value + message;

            Dts.TaskResult = (int)ScriptResults.Success;
        }
    }
}

Screenshot #1:

Screenshot #2:

Screenshot #3:

Screenshot #4:

Screenshot #5:

Screenshot #6:

Screenshot #7:

Screenshot #8:

Screenshot #9:

Screenshot #10:

Step-1 : Create a Object data type variable which will hold the result set of a given table and a string type variable to hold the recipient email IDs. variable declaration

Step-2 : Use a Execute SQL Task Editor and choose the Result set option as Full result set and a query statement to fetch the data from a given table. Use the object variable to hold the result set of your table as shown in screen shots below:

screen shot-1 screen shot-2

Step-3 :Take a Script task Editor and use Recepient_email_id variable as read only variable and Use the below C# script to send an email.

#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Linq;
using System.Collections.Generic;
using System.Collections;
using System.Data.OleDb;
using System.Net.Mail;
using System.Net;
#endregion

namespace ST_a5f34f5fc36645b6bd90a5b8887ac589
{
    /// <summary>
    /// ScriptMain is the entry point class of the script.  Do not change the name, attributes,
    /// or parent of this class.
    /// </summary>
    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {

        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        }

        public void Main()
        {
            Variables varCollection = null;

            string User_Recepient_Email_ID = Dts.Variables["User::Recepient_Email_ID"].Value.ToString();

            Dts.VariableDispenser.LockForWrite("User::Test_Table_Result");
            Dts.VariableDispenser.GetVariables(ref varCollection);
            var data = varCollection["User::Test_Table_Result"].Value;

            OleDbDataAdapter da = new OleDbDataAdapter();
            DataTable dt = new DataTable();
            da.Fill(dt, varCollection["User::Test_Table_Result"].Value);

            SendMailMessage("[email protected]", User_Recepient_Email_ID, "ETL Load Status Report",ConvertDataTableToHTML(dt),true,"174.18.10.122");

            Dts.TaskResult = (int)ScriptResults.Success;
        }


        public static string ConvertDataTableToHTML(DataTable dt)
        {
            string html = "<table border ='1'>";
            //add header row
            html += "<tr>";
            for (int i = 0; i < dt.Columns.Count; i++)
                html += "<th>" + dt.Columns[i].ColumnName + "</th>";
            html += "</tr>";
            //add rows
            for (int i =0;i<dt.Rows.Count;i++)
            {
                html += "<tr style='color:blue;'>";
                for (int j = 0; j < dt.Columns.Count; j++)
                    html += "<td>" + dt.Rows[i][j].ToString() + "</td>";
                html += "</tr>";
            }
            html += "</table>";
            return html;
        }
        private void SendMailMessage(string From, string SendTo,string Subject, string Body,bool IsBodyHtml,string Server)
        {
            MailMessage htmlMessage;
            SmtpClient mySmtpClient;

            htmlMessage = new MailMessage(From, SendTo, Subject, Body);
            htmlMessage.IsBodyHtml = IsBodyHtml;

            mySmtpClient = new SmtpClient(Server);
            mySmtpClient.Credentials = CredentialCache.DefaultNetworkCredentials;
            mySmtpClient.Send(htmlMessage);
        }   


    }
}

Please note : You need to change the sender email id, email subject and server IP address for the below statement in above script.

SendMailMessage("[email protected]", User_Recepient_Email_ID, "ETL Load Status Report",ConvertDataTableToHTML(dt),true,"174.18.10.122");

Below is the format of email which you receive. screen shot-3

You can apply different approach.. Just execute a SQL Database task from SSIS to execute SP "only if you specifically want SSIS step to be executed" (or you can just create SPand schedule it in job scheduler that it). and inside the SP you can send email. in case ,if you dont know how to setup email profile refer this (https://www.codeproject.com/Articles/485124/Configuring-Database-Mail-in-SQL-Server)

example as below -

CREATE TABLE #Temp 
( 
  [Rank]  [int],
  [Player Name]  [varchar](128),
  [Ranking Points] [int],
  [Country]  [varchar](128)
)


INSERT INTO #Temp
SELECT 1,'Manoj Kargeti',12390,'India'
UNION ALL
SELECT 2,'Vimal Kumar',7965,'Nepal'
UNION ALL
SELECT 3,'Pappu Djokovic',7880,'ShriLanka'


DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)


SET @xml = CAST(( SELECT [Rank] AS 'td','',[Player Name] AS 'td','',
       [Ranking Points] AS 'td','', Country AS 'td'
FROM  #Temp ORDER BY Rank 
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))


SET @body ='<html><body><H3>Tennis Rankings Info</H3>
<table border = 1> 
<tr>
<th> Rank </th> <th> Player Name </th> <th> Ranking Points </th> <th> Country </th></tr>'    


SET @body = @body + @xml +'</table></body></html>'


EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SQL ALERTING', -- replace with your SQL Database Mail Profile 
@body = @body,
@body_format ='HTML',
@recipients = '[email protected]', -- replace with your email address
@subject = 'E-mail in Tabular Format' ;


DROP TABLE #Temp

How to send the records from a table in an e-mail body using SSIS package?

Related

Recent Posts