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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Paul Clancy » Sending scheduled SQL query results via HTML...

82 posts, Page 8 of 9

«««5 6 7 8 9 »»

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

Rate Topic

Display Mode

Topic Options

Author

Message

Geoffrey Doebler

Posted Wednesday, November 02, 2011 7:08 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 3:02 PM
Points: 34, Visits: 307

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).

Post #1199262

marco.yandun

Posted Wednesday, November 02, 2011 8:32 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, November 30, 2011 7:44 AM
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 BigGrin

Marco

Post #1199322

shadidiaz

Posted Monday, November 21, 2011 12:58 PM

Forum Newbie

Group: General Forum Members
Last Login: Friday, September 28, 2012 8:29 AM
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

Post #1209613

marco.yandun

Posted Monday, November 21, 2011 1:24 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, November 30, 2011 7:44 AM
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)

Post #1209621

shadidiaz

Posted Monday, November 21, 2011 2:07 PM

Forum Newbie

Group: General Forum Members
Last Login: Friday, September 28, 2012 8:29 AM
Points: 4, Visits: 302

Thanks marco.yandun

That will work for me.

Post #1209635

bradley.deangelis

Posted Wednesday, January 04, 2012 11:01 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, January 31, 2013 11:32 AM
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.

Post #1230182

David McKinney

Posted Thursday, January 05, 2012 12:47 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 12:43 AM
Points: 583, Visits: 1,610

..probably permissions related. Does the account under which the sql agent service is running have the necessary permissions on the database?

Post #1230522

joraim

Posted Tuesday, April 16, 2013 3:43 PM

Forum Newbie

Group: General Forum Members
Last Login: Friday, April 26, 2013 10:05 AM
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

Post #1443033

Paul Clancy

Posted Wednesday, April 17, 2013 12:19 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 12:48 PM
Points: 41, Visits: 175

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

Post #1443106

joraim

Posted Thursday, April 25, 2013 12:32 PM

Forum Newbie

Group: General Forum Members
Last Login: Friday, April 26, 2013 10:05 AM
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

Post #1446671

« Prev Topic | Next Topic »

82 posts, Page 8 of 9

«««5 6 7 8 9 »»

Permissions