SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Sending scheduled SQL query results via HTML e-mail using SSIS


Sending scheduled SQL query results via HTML e-mail using SSIS

Author
Message
Geoffrey Doebler
Geoffrey Doebler
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 Visits: 386
Is it the "Report Data" window you're missing? It shows you the Built-in Fields, Parameters, Images, Data Sources and Datasets? If so, try Ctrl + Alt + D. Or View > Report Data (at the bottom).
marco.yandun
marco.yandun
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 19
That's just it Geoffrey!!

Thank you very much for your help. Your advice just saved me a lot of time and headaches :-D

Marco
shadidiaz
shadidiaz
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 302
Nice article it got me going with SSIS and E-mail

I have a question is it possible to send Multiple Recipent. If we add recipent e-mail address in the variable "varMailTo" and How??

I Tried with xxx@aaa.com;sss@ss.com but the package failed 1 email works fine.

Thanks again

Steve
marco.yandun
marco.yandun
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 19
Yes you can, try with the following code:

Dim varHTMLMail As MailMessage
Dim varSMTPClient As SmtpClient
Dim varMailBody As Object
Dim varAddresses As String

varMailBody = Dts.Variables("varProductionSummaryHTML").Value
varAddresses = Dts.Variables("varMailTo").Value.ToString

varHTMLMail = New MailMessage()
varHTMLMail.From = New MailAddress("name1@xx.com")
varHTMLMail.Subject = "xxxxxxx"
varHTMLMail.Bcc.Add("name2@xx.com")
varHTMLMail.Bcc.Add("name3@xx.com")
varHTMLMail.Body = varMailBody
varHTMLMail.IsBodyHtml = True
varSMTPClient = New SmtpClient("mail.acitus.com") 'Your_SMTP_Server_Name
varSMTPClient.UseDefaultCredentials = True
varSMTPClient.Send(varHTMLMail)
shadidiaz
shadidiaz
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 302
Thanks marco.yandun

That will work for me.
bradley.deangelis
bradley.deangelis
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 7
This article was extremely helpful, but I have run into a snag.

This works great and it sends me the email I need when executing in BIDS, but once I schedule the package as a Job in SQL Server 2008 the email gets sent to me blank.

Any suggestions would be fantastic.
David McKinney
David McKinney
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2811 Visits: 2090
..probably permissions related. Does the account under which the sql agent service is running have the necessary permissions on the database?
joraim
joraim
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 10
Paul

Thanks a lot for this contribution, this will help me to sent my employee their check balance monthly, extracting data from our ERP.
Just a little issue cause on the HTML I have images and those are referred over the a www site accesible for anywhere, but when I receive the email, the mail cames without those images, and there is no secure waring or option to download the images.

What can be the issue?, is this just plain HTML (tables, styles) or can be better?

Also the mailTo variable, can I update this variable from a SQL Task to extract the value and modify this also the subjetct of the mail?

Thank
Paul Clancy
Paul Clancy
SSC Veteran
SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)

Group: General Forum Members
Points: 294 Visits: 196
Updating a variable from an Execute SQL Task is pretty straightforward; choose your connection, enter your SQL command (in this case, any statement that returns one value, this being the subject line or variable portion thereof), choose the result set type (single row in this instance), then on the Result Set tab select the variable that you're mapping the statement result to from the list and you're set.

More details here: http://technet.microsoft.com/en-us/library/cc280492.aspx
joraim
joraim
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 10
Thanks, this variable set appears to be simple to do, now I have my main concern about the mail, I have all working but the content just send plain text, no HTML I have images on my HTML taht is contruct with the xml passing thru xslt and the images never cames on the mail, what can be the solution, thanks

Regards
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search