SQL : Send email based on the last file load time

kkran 831 Reputation points
2023-07-10T22:11:13.3266667+00:00

Hi Team - We load files through ETL-SSIS and log the file names and load date time when the file is loaded.

Based on the most recent load_datetime, if the filename was logged in this table with in 32 hours then send an email to users. For example the recent datetime is '2023-07-10 01:00:07.030', if we haven't logged the file into this table in 32 hours which is by '2023-07-11 07:00:07.030, then send an email. could you please help me achieve with this T-SQL (Database mail) ?

User's image

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,226 questions
0 comments No comments
{count} votes

Accepted answer
  1. CosmogHong-MSFT 19,906 Reputation points Microsoft Vendor
    2023-07-11T03:40:46.6833333+00:00

    Hi @kkran You could create job to send alert using sp_send_dbmail based on the max load_datetime.

    The query will be like this:

    USE DatabaseName
    GO
    DECLARE @load_datetime datetime
    
    SELECT @load_datetime=MAX(load_datetime) 
    FROM TableName 
    
    IF DATEDIFF(HOUR,@load_datetime,GETDATE()) >= 32
    BEGIN
      EXEC msdb.dbo.sp_send_dbmail
      @recipients=N'xxxxx@xxxxx.com',
      @body='32 hours no file loaded',
      @subject ='32 hours no file loaded',
      @profile_name ='xxxxx',
      @query =  '(select top(10) * from DatabaseName.dbo.TableName order by load_datetime DESC)'
    END
    

    Then create a scheduled job that will check every hour if the time difference is greater than 32 hours.

    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

0 additional answers

Sort by: Most helpful