SQL : Send an email notification to users

kkran 831 Reputation points
2023-05-22T20:39:46.1366667+00:00

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]
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,315 questions
{count} votes

Accepted answer
  1. LiHongMSFT-4306 25,651 Reputation points
    2023-05-23T05:50:13.49+00:00

    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.

    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Erland Sommarskog 106.5K Reputation points
    2023-05-22T21:47:53.3433333+00:00

    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.

    1 person found this answer helpful.
    0 comments No comments