SQL : Send an email notification to users

Question

Hi All - we process a file whenever the user uploads the file in shared drive through ETL-SSIS. Once the file is processed we store the details in the SQL table. Below is the information we store:

based on the trend the users uploads the file to the shared path every day. But there are cases user may not upload the file to shared drive for almost 36 hours. So in that case we want to send an email to the users since the last file Load_datetime notifying that we haven't received the file in the last 36 hours. Lets say if the user last uploaded the file on 5/21 7 Pm and if we have not received any file by 5/23 7 am then we should send an email. Could you please let me know how this can be done? Thank you in advance.

     ,[file_name]
      ,[source_file_path]
      ,[archive_file_path]
      ,[load_datetime]
      ,[load_status]

Answer 1

Hi @kkran

This article describes how to enable and configure Database Mail using the Database Mail Configuration Wizard and create a Database Mail Configuration script using templates.

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Schedule something that runs from Task Scheduler or Agent let's say once an hour, and checks for missing uploads and sends a mail. This something can be written in PowerShell, C# or whatever you fancy. And, yes, it could be a T-SQL task that uses database mail, but if you are not using database mail already, there is little reason to get into that complexity.