Using SSIS to send query results via email

Question

I have a task to send query result in an email using SSIS 2017. I have referred to previous posts related to that but i am not able to get my required answer. Here's what i have done so far: 1. Create an executive sql task with my query and resultset which refers to a variable of object type 2. create a foreach container 3. placed a script task under foreach loop(i think my issue is with this task) and have placed the variable as readonlyvariable 4. send email task with bodytext as variable

My result set is just one column from the table.

I have referred to this url: How to send the records from a table in an e-mail body using SSIS package?

and please find attached the script task code.

Would be great if you guys could help me out

/*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_8015f41e93944f0e944089c73b520312
{
    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    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::Result");
            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}\n", "Result");
                //varCollection["User::EmailMessage"].Value = header;
            }
            //Format the query result with tab delimiters
            message = string.Format("{0}",
                                        varCollection["User::Result"].Value);

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

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

Can you just create a stored procedure that utilizes DB mail and then execute the stored procedure in SSIS/SQL Agent job/Manual ? — Khal_Drogo
You can create one stored procedure and inside that set output parameter for email body from table in html format. This can directly use in script task. This is the easiest solution to show table data in email. — Anagha
Hi Khal, unfortunately we don't have privileges to run db mail through a stored proc — Akshat Shreemali
@ Anagha: can you provide a sample of this? i am new to this ssis thing and currently stuck with it — Akshat Shreemali

bullock_william bullock_william · Accepted Answer · 2020-12-31T15:13:48

I was able to accomplish this without using a script task or db mail. I needed to email myself notices of any reports that failed to refresh across multiple reporting systems. But I didn't want to save the results to a file and attach the file. I just wanted the results in the email body.

I wrote my query to output one concatenated column and added the query results to a recordset Object and used a foreach loop container to append each row to a string variable with "/n" to start a new line.

This created a string that I could use as the body of the email.

Data Flow

Control Flow

Using SSIS to send query results via email

1 Answers