ssisssmssql-agent-job

SSIS package run by SQL Agent stuck for 7 days without timing out. How can I prevent this?


I have an SSIS package that is pulling data using an ODBC source -> OLE DB dest. Very simple package that only has a few tools in it. Typically the package takes ~45 minutes to copy 14 million rows. In the job log it seems like it got stuck on Validation, meaning it didn't even start executing, so in theory a connection timeout wouldn't have helped here.

Is there a universal SSIS package timeout option I can build in?

enter image description here


Solution

  • Simplest way to do this would be to create a 2nd monitoring job to watch/check this main job.

    On this monitoring job you would set the schedule run date/time to start/run at a time that is XXX minutes after you would expect the main job to complete (and give some extra minutes to ensure even the longest run time you would expect it to complete by).

    So your main job starts at 1AM, you expect it to be done by 130am. I would set this monitor/kill job to run say 230AM (to give you an extra hour just in case it does run a bit longer).

    Then you would just have the monitor job run this script:

    EXEC msdb.dbo.sp_stop_job @job_name = 'JobNameToKill'
    

    You could add this as well so you know the job was killed and you will get an email:

    EXEC msdb.dbo.sp_send_dbmail 
                        @recipients = 'EmailAddress',
                        @subject = 'EmailSubject',
                        @body = 'EmailBody',
                        @importance = 'NORMAL',
                        @body_format = 'HTML';