Hi all, i am fairly new to SSIS and have managed to get a foreach loop container looping through a recordset to email data out to various sources. My issue today is that i have not been able to get the contents of 1 entire record set into the body of 1 single email, whether plain text or HTML. I have tried a number of varaitions and methods but am not getting anywhere. I have looked over the web and cannot find anything useful either. Please note that i have been able to use a data flow to export the resultset to a flat file connection and then attach that to an email but would prefer to stay away from the "filesystem" if possible. Can someone offer any help?

One way to accomplish this is to access the recordset programmatically in a script task and shred it to generate your message body. Assign the generated message to a package variable. Use that variable as the "message source" from the send mail task. Here is a good example that might help you... http://sqlblog.com/blogs/andy_leonard/archive/2007/10/14/ssis-design-pattern-read-a-dataset-from-variable-in-a-script-task.aspx Hope this helps, Senthil

Take a step back, have you considered using something like Reporting Services instead. Producing a report and emailing it may be a more sensible and flexible approach than trying to build your own engine that replicates that functionality in SSIS. It may not suit your overall scenario, but on the surface the technology fit is better that SSIS.

Thankyou both for your replies.. both have been very useful. Cheers.

I'll just add one more option here for you, which I've used. Basically, you query your DB with a FOR XML option (works best on 2005+), and then run the XML through an XSLT and then email the resulting HTML. To do this requires 3 tasks (Execute SQL Task, XML Task & Script Task), though, as well as an XSLT document, but I think you should be able to store that information in a variable. http://www.sqlservercentral.com/articles/SSIS/62678/

Hi Senthil, I followed the example on http://sqlblog.com/blogs/andy_leonard/archive/2007/10/14/ssis-design-pattern-read-a-dataset-from-variable-in-a-script-task.aspx. But, I have been getting the error: "Error: The script threw an exception: Object is not an ADODB.RecordSet or an ADODB.Record. Parameter name: adodb at System.Data.OleDb.OleDbDataAdapter.FillFromADODB(Object data, Object adodb, String srcTable, Boolean multipleResults) at System.Data.OleDb.OleDbDataAdapter.Fill(DataTable dataTable, Object ADODBRecordSet) at ScriptTask_164e0d9976794196bb0ab2d7dbea1ca6.ScriptMain.Main() " My source database is Oracle, and my connection type is "OLE DB". I have Execute SQL Task-->Script Task. Execute SQL Task runs successfully and parses value to the variable named "UnrecognizedOrg" with "Object" data type. I set Result Name = 0 (zero) for this variable. It blows up in Script Task which takes the result set from "UnrecognizedOrg" variable above. This variable is set as "ReadWriteVariables". Here is my troublesome code snippet. The last line is where the code breaks. Is there anything I am missing here? I have been unsuccessfully searching and searching for the solution for more than a week... Thank you very much in advance for your thought! Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Runtime Imports System.Xml Imports System.Data.OleDb Public Class ScriptMain Public Sub Main() Dim oleDA As New OleDb.OleDbDataAdapter Dim dt As New DataTable Dim col As DataColumn Dim row As DataRow Dim sMsg As String oleDA.Fill(dt, Dts.Variables("UnrecognizedOrg").Value) 'This is where it blows up..

Hi Senthil, Is there a way to send e-mail only when the variable is not empty? By doing it through Send Mail Task, I believe it is going to send e-mail every single time. I need to send an alert e-mail only when there is something in my recordset/message. Thank you for your suggestion.

Hi Senthil, Is there a way to send e-mail only when the variable is not empty? By doing it through Send Mail Task, I believe it is going to send e-mail every single time. I need to send an alert e-mail only when there is something in my recordset/message. Thank you for your suggestion. If the variable containing your message is type string, you can setup the following constraint expression: LEN( TRIM( @[User::<your variable>] ) ) > 0SSIS Tasks Components Scripts Services | http://www.cozyroc.com/