Solved

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

Posted on 2014-03-12
2
1,477 Views
Last Modified: 2016-02-10
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
Comment
Question by:printmedia
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 37

Accepted Solution

by:
ValentinoV earned 500 total points
ID: 39925730
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
 

Author Closing Comment

by:printmedia
ID: 39926895
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

My client sends data in an Excel file to me to load them into Staging database. The file contains many sheets that they have same structure. In this article, I would like to share the simple way to load data of multiple sheets by using SSIS.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question