Solved

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

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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video discusses moving either the default database or any database to a new volume.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now