Thank this author by sharing:
By Paul Clancy,
2010/09/03 (first published: 2008/04/23)
Some time ago I was creating an SQL Server Integration Services package to pump data from one SQL system to another. One of the functions of the package was to delete records on the destination system that had changed status on the source system. This delete action was not possible if child records existed in other tables of the destination database, so I went about figuring out if it was possible to automatically notify users that these records had not been deleted via e-mail. They needed to get full details in a user-friendly format so they could take further action, so HTML mail was the desired means of delivery.
The package I came up with to achieve this can be used to mail any suitable SQL result set and proves once again the flexibility of SSIS and the different ways it can be used to solve a variety of problems beyond its everyday use as an ETL tool.
This example queries the SQL 2005 AdventureWorks database and returns sales figures for a given date in a formatted e-mail.
You'll need a copy of the SQL 2005 AdventureWorks database running on a test server.I'm assuming that you're familiar with using the SQL BI Dev Studio tools and building basic packages. If this isn't the case, I recommend working your way through the Integration Services Tutorial in SQL Books Online to become familiar with the basic concepts.
This package makes use of a basic XSL transformation to massage the SQL XML query results and transform them into a user-friendly HTML format. I'm not going to go into any detail regarding XSL, but some useful links are listed at the end of the article if you're curious to learn more.
Click “Design Script” to open the SSIS VSA editor. Replace the default (basis) code in the editor with the code listed below:Option Strict OffImports SystemImports System.DataImports System.MathImports System.Net.MailImports Microsoft.SqlServer.Dts.RuntimePublic Class ScriptMainPublic Sub Main()Dim varHTMLMail As MailMessageDim varSMTPClient As SmtpClientDim varMailBody As ObjectDim varAddresses As StringvarMailBody = Dts.Variables("varSalesSummaryHTML").ValuevarAddresses = Dts.Variables("varMailTo").Value.ToStringvarHTMLMail = New MailMessage("firstname.lastname@example.org", varAddresses, "Daily Order Summary", varMailBody)varHTMLMail.IsBodyHtml = TruevarSMTPClient = New SmtpClient("Your_SMTP_Server_Name")varSMTPClient.UseDefaultCredentials = TruevarSMTPClient.Send(varHTMLMail)Dts.TaskResult = Dts.Results.SuccessEnd SubEnd ClassNote the extra namespace reference to System.Net.Mail which we need in order to access the mail methods.
If the package runs successfully, you're finished. If it fails on the last step though you'll want to check a few things. There's no room in this article to go into the intricacies of configuring SQL to send mail, but you can at least work through the following checklist:
In other words, there are a number of factors outside SSIS that can prevent you from sending mail successfully, but I'm afraid that you'll need to liaise with your network/mail administrator to solve these!
That said, this is still a simple and powerful means of mailing live data to your users. You could take it further and build your Mailing list from another SQL query, or combine multiple result sets in one mail. Deploy the package on your SSIS server, schedule it as desired and you're done.
Query results splitter
This script will query across all databases, returning a result of objects recently created or modif...
script to return query result transposed.
Creating view from stored procedure results
Before I delve into the subject of scripting SSIS package deployments, I’m going to take a slight de...
As a member of SQLServerCentral, you get free access to loads of fresh content: thousands
of articles and SQL scripts, a library of free eBooks, a weekly database news roundup,
a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals
that makes it such a success.