My ETL process sends mail when it's done - yours probably does too. I like to send a little additional information along in that email - not just "I'm done" or "I failed". It's informative to send information like how long it took, how many records of particularly sensitive tables were processed, what the errors were - that kind of thing. I find that it's read best in a table format - particularly with a little subtle color thrown in to highlight issues.
The Send Mail Task Works...
Sure it does... but unfortunately I'm not big into making tables with those funky characters like we all used to do in the 80s. Especially not when we have HTML today. And despite the incredible engine that the SSIS Team provided for us - they weren't quite able to stock the pantry with everything you need for day to day cooking, if you know what I mean.
The Send HTML Mail Task
Almost the same as the Send Mail Task - but so much prettier, if I do say so myself. All the options of the Send Mail Task, plus you're able to specify a "reply to" address, and set the Task to send HTML mail. (It will also send plain-text mail as well.)
The Task uses code largely identical to what you'll find in Script Tasks that have been helpfully posted around the internet - this one being an example.
Head on over to CodePlex to download the installer and/or source code!
Tood, what about the ability to configure expressions for mail variables?
ReplyDeleteYou should be able to do that - select the Task on the Control Flow and press F4 to open the properties window. Select the "Expressions" property, and click on the ellipsis (...) button. Go crazy...
ReplyDeleteIf the body isn't filled in then it says there's an error. Maybe a suggestion to either remove it or just have it as a warning? There may be times when you want to send an email without a body.
ReplyDeleteAttachments in MS Mail Task are seperated by pipes, which is easy enough. When using expressions to set the attachment list for the HTML Mail Task, what would be the deliminator to use?
If switching between HTML and Plain Text, you loose any body content which is a slight shame. Nothing too bad.
Todd, thank you very much! It is a shame that the MS Mail Task just didn't go that extrea step, your code makes the leap.
Ahhhh.... semi colon's for the attachment seperation if using an expression.. if I am looking at the source code correctly!
ReplyDeleteMy bad!
Thanks for the info, Kevin.
ReplyDeleteI just took the time to update the project on CodePlex - I'd been using some slight improvements for a little while. It's now possible to give it a body with more than 4000 characters (you need to use a variable, and assign the variable without using Expressions). I haven't made the ones you'd asked for - perhaps I should change the "error" of no body to a "warning" instead.
I will put some info up about the delimiter for multiple attachments - thanks for pointing that out. Glad you like it!
Hi Todd - Thank you for sharing this tool. It's working great for me with internal email addresses, but is having issues with external. I'm getting the following error:
ReplyDeleteExecution error: System.Net.Mail.SmtpFailedRecipientException: Mailbox unavailable
Interestingly, I've got Send Mail tasks within the same package using the same SMTP connection that do not fail this way. Any thoughts?
Not really, Anon.
ReplyDeleteIt doesn't use credentials, looks for port 25, and uses System.Net.Mail. I would go talk to your mail admins to see what that kind of error means to them - it might point you in the right direction.