locked
How to Sent SSIS email on package running more then an hour RRS feed

  • Question

  • can anyone in group suggest me how can I do When an SSIS package has been running for more than 1 hour an email should be sent? and I have multiple packages. It's urgent. thanks
    Friday, March 3, 2017 6:31 AM

All replies

  • Hi Mohammad

    Assuming you are talking about executing SSIS packages and not just SQL Agent jobs in general then you will be able to query the catalog views for this information. you can query [catalog].[executions] to get jobs that have been running for over an hour and have not yet completed. this would look something like this

    SELECT * FROM catalog.executions AS E WHERE E.end_time IS NULL and DATEDIFF(MI,E.start_time,getdate()) > 60

    You could then use database mail to send an e-mail if there are any jobs that have been running for over an hour. That could look something like this

    IF EXISTS(SELECT * FROM catalog.executions AS E WHERE E.end_time IS NULL and DATEDIFF(MI,E.start_time,getdate()) > 60)
    BEGIN
    	EXEC msdb.dbo.sp_send_dbmail  
    		@profile_name = '{MailProfile}',  
    		@recipients = 'me@work.com',  
    		@query = 'SELECT * FROM catalog.executions AS E WHERE E.end_time IS NULL and DATEDIFF(MI,E.start_time,getdate()) > 60' ,  
    		@subject = 'Work Order Count',  
    		@attach_query_result_as_file = 1 ;  
    END

    # you would want to put some effort into displaying a usefull message.

    You would then take this query and put it in a SQL agent job and set it to execute every 5 minutes or so.


    Friday, March 3, 2017 9:11 AM