How to send a result set from a request as an attachment in an email using SSIS?

Question

I have a result set from an Execute SQL Task query stored in a System.Object variable, and I would like to send these results using the Send Mail Task using the following expression in MessageSource.

"Please find the attached data summary \ n \ n" + SUBSTRING (@ [User :: myVariable], 1,3990)
+ "\ n \ n"

Answer 1

You have probably already found the answer to your question. This answer is for others who may stumble upon this question. I do not think you can use an object variable in an expression. You need to go through the query result object and format it to a string so that you can send the query output in an email message. You can also export data to a file and send the file as an attachment. This is another possible option. This example shows how to cycle through a set of query results to form the body of a message, which will then be sent by e-mail using the Send Email task.

Step by step:

• Create a table called dbo.EmailData using the script in the SQL Scripts section.

• Screenshot # 1 shows sample data that Execute SQL sets up a query and sends it an email in this example.

• In the SSIS package, create variables 5 , as shown in screenshot # 2 .

• In the SSIS package, place the following tasks: Execute SQL task , Foreach loop container , Script task in the Foreach loop container , and Send Email task .

• Configure the Execute SQL task as shown in screenshots # 3 and # 4 .

• Set up Foreach loop container as shown in screenshots # 5 and # 6 . The Mapping Variables section shows the order in which the query result columns are displayed and their purpose for SSIS variables. These variables will be used to compose an email message inside the Script task .

• In the Script task replace the code with the one shown in the Script section of the task code . The script task has very simple text formatting of email messages.

• Set up the email sending task as shown in screenshot # 7 . You need to configure it with a valid email address in From and B.

• After setting up the control flow tasks, your package should look like the screenshot # 8 shows.

• An example of package execution is shown in screenshot # 9 .

• The email sent by the package is shown in screenshot # 10 . Some information has been removed from the screen. You can compare the table data shown in screenshot 1 with this email output and they should match.

Hope this 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 # , which can only be used 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 No. 2:

Screenshot 3:

Screenshot 4:

Screenshot No. 5:

Screenshot No. 6:

Screenshot No. 7:

Screenshot # 8:

Screenshot No. 9:

Screenshot No. 10:

Answer 2

After many attempts, this method really works. However, if you have Visual Studio 2010 or higher, the C # script needs to be changed so slightly.

Replace:

[System.AddIn.AddIn ("ScriptMain", Version = "1.0", Publisher = ", Description =" ")]

with this:

[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]

Still trying to figure out the gap in the letter, I get, but am quite pleased with the results.