October 3, 2016 at 1:32 pm
In a sql server 2012 database, I need to run a query and check to see if there is more than 1 record per student for the current school year in a table called studentdata.
I cannot put a trigger on the column in the table that shows there is more than 1 record for the student in the current school year since the data is generated using vendor software.
The vendor will not support the applicaton if I change there software.
Thus to check for duplicates and email the appropirate schoool staff, I have the following questions:
1. To send out the email messages, is it better to use outlook email or sql server email? Would you tell me one why one method is better than the other? In addition, would you show me the sql to use and/or point me to a refernce that will show me how to setup the email message?
2. To have this sql executed, would it be better to place the sql in an existing sql job that is run every night between a Monday or Friday night? Otherwise would it be better to have this sql generated as a scheduled job? If this is a scheduled job, would yoyu set this up as an SSIS package? The only purpose would be to send out the email message and an applicable report? Would you show me what method is better and tell me why that method is better? In addtion would you show me the sql on how to setup this job and/or point me to urls (links) that will tell me how to accomplish this goal?
October 4, 2016 at 5:06 am
This doesn't really sound like an SSIS task, no. Generally SSIS is used for ETL, and what you're doing here is simply checking the data and notifying someone via email. There's no loading or transformation, so this would be much simpler in SQL as a Stored Procedure.
As a quick reference though, SSIS does not support sending emails in HTML (at least not natively up to 2012), where as SQL can send html formatted emails.
The sp you're looking for in SQL will be sp_send_dbmail, the MSDBpage should tell you everything you need to know, as it's a very simple procedure to use. It also gives you an example of how to turn your dataset into a html formatted table.
You will need to ensure you have Database Mail configured first as well, if you have not already.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 4, 2016 at 5:50 am
Thom A (10/4/2016)
This doesn't really sound like an SSIS task, no. Generally SSIS is used for ETL, and what you're doing here is simply checking the data and notifying someone via email. There's no loading or transformation, so this would be much simpler in SQL as a Stored Procedure.As a quick reference though, SSIS does not support sending emails in HTML (at least not natively up to 2012), where as SQL can send html formatted emails.
The sp you're looking for in SQL will be sp_send_dbmail, the MSDBpage should tell you everything you need to know, as it's a very simple procedure to use. It also gives you an example of how to turn your dataset into a html formatted table.
You will need to ensure you have Database Mail configured first as well, if you have not already.
Good advice. I agree about SSIS and e-mails ... I send e-mails from SSIS by calling a proc which also does the HTML formatting.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 4, 2016 at 7:22 am
Phil Parkin (10/4/2016)
I send e-mails from SSIS by calling a proc which also does the HTML formatting.
I do the same when in SSIS. I would actually prefer, at least in some cases, to not do so (in my mind it looks odd seeing a SQL task in the Work Flow named "Send Confirmation Email"), but html formatted emails look so much more malleable, and professional. Especially when you're sending email outside of your own business.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply