Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««56789»»

Sending scheduled SQL query results via HTML e-mail using SSIS Expand / Collapse
Author
Message
Posted Wednesday, November 2, 2011 7:08 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, October 22, 2014 1:37 PM
Points: 34, Visits: 322
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
Posted Wednesday, November 2, 2011 8:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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

Marco
Post #1199322
Posted Monday, November 21, 2011 12:58 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Monday, November 21, 2011 1:24 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Monday, November 21, 2011 2:07 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Wednesday, January 4, 2012 11:01 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Thursday, January 5, 2012 12:47 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Monday, December 15, 2014 9:36 AM
Points: 652, Visits: 1,901
..probably permissions related. Does the account under which the sql agent service is running have the necessary permissions on the database?
Post #1230522
Posted Tuesday, April 16, 2013 3:43 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Wednesday, April 17, 2013 12:19 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 18, 2014 12:01 PM
Points: 43, Visits: 191
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
Posted Thursday, April 25, 2013 12:32 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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 »

Add to briefcase «««56789»»

Permissions Expand / Collapse