SQL : Send email based on the last file load time

Question

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

Answer 1

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.