• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1546
  • Last Modified:

SSIS package in SQL Server 2008 to send email alert with records in email body

Hi all.

I would like to create a SSIS package that runs daily that runs a select query, if that query returns any results that send those results in an email to a specific email address. How can I do this in SQL Server 2008?

We used to have SQL Server 2000 and used to do it using an ActiveX Script, the script would create an html table in the email body with the records, I noticed it's available in SQL Server 2008 but it asks me to install the DTS 2000 runtime. That tells me it still allows us to use ActiveX scripts but I don't want to use features that will eventually be deprecated in future versions of SQL Server when we upgrade so I'd rather use the tools available in SQL Server 2008.

So how can I do this using SQL Server 2008 without using ActiveX Script?

Thank you in advance.
0
printmedia
Asked:
printmedia
1 Solution
 
ValentinoVBI ConsultantCommented:
You can achieve similar results by using the Script task.  Here's how that Control Flow would look like:

1. Execute SQL Task - result set goes into package variable of type Object

This task fetches the data needed to build the HTML table.

2. Script Task - pass Object variable into it through ReadOnlyVariables, put a package variable of type String in the ReadWriteVariables.  Use OleDbDataAdapter to access the data in the Object variable and build the HTML table.

Here's some code to help get you started:

//using System.Data.OleDb;

object dataset = Dts.Variables["YourObjectVariable"].Value;

OleDbDataAdapter adapter = new OleDbDataAdapter();
DataTable dt = new DataTable();
adapter.Fill(dt, dataset);

foreach (DataRow row in dt.Rows)
{
	//process rows
	
	foreach (DataColumn col in dt.Columns)
	{
		//process columns in row
	}
}

Dts.Variables["YourStringVariable"].Value = <some_string_built_in_loop_above>;

Open in new window

Once that's complete you've got your email body waiting in the package variable, ready to be sent.
0
 
printmediaAuthor Commented:
Thanks. You directed me towards the right path. Instead I created a SQL task that first checks if there are records, if there are then it creates the HTML table and then sends it using sp_send_dbmail.
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now