Solved

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

Posted on 2014-03-12
2
1,462 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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

Suggested Solutions

My client sends a request to me that they want me to load data, which will be returned by Web Service APIs, and do some transformation before importing to database. In this article, I will provide an approach to load data with Web Service Task and X…
I have a large data set and a SSIS package. How can I load this file in multi threading?
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

738 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