?
Solved

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

Posted on 2014-03-12
2
Medium Priority
?
1,518 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 2000 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

777 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