Ssis and email

SSIS and Import/Export
1

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?
2

do you have an email server internally you will be using for sending the email? Exchange or some other mailing server?
Do you have an SMTP server? because when using sql mail you will need to point it to one.

  1. You can use either 'outlook' or sql server (which requires an SMTP server)

  2. SSIS is overkill unless you are doing something. I Would just stick to simple SQL job. A SQL job can be pointing to an SSIS or just TSQL code.

if you search in this forum for keywords you will find all you will need.