Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I've an SSIS task which download a couple of files,update various Database tables and archive the files(i have it split in various packages).
The average time to completion is about 2.30 min.

However, i've recently noticed on few occassions that the SSIS Task does remain in the runnning state and unable to complete.

What i would like is to receive an email when the average time to completion is passed and after fail the package .

share|improve this question
3  
This question appears to be off-topic because the problem is isolated to a 3rd-party product/solution. Please raise the issue with the vendor's support system. –  Jon Seigel Oct 24 '13 at 15:31
2  
"How can I detect when an SSIS package's execution exceeds its standard processing threshold" might be a good question for here though-if you ignore that it's some thirdparty tool causing the overrun. I then though it might even be better as a generic "my X is running long" where X is ssis package, agent job, proc etc. but then it'd risk being too broad. –  billinkc Oct 24 '13 at 18:09
    
@JonSeigel Thanks for the remark but my issue is not with the cause of the long running(that a different topic) rather with me being informed when the threshold period is passed as mentioned by billinkc –  Raymond Oct 24 '13 at 19:44
    
@raymond Edit the question to indicate the above is your core problem so that folks don't pull the trigger and close this question thinking it's a 3rd party problem. You're not the first person to ask this sort of question and I think it's an interesting nut to crack. –  billinkc Oct 25 '13 at 2:35

1 Answer 1

My off the cuff thought would be that you need to turn on logging in your SSIS package and I'm assuming you have it pointed at SQL Server. I prefer to capture OnPre/PostExecute, TaskFailed and OnError events at a minimum.

Write a query that looks for packages that have begun execution but have not terminated. This will go against dbo.sysdtslog90 for 2005, dbo.sysssislog for 2008+ instances.

Depending on how you like to solve your problems, you can use that query in pure TSQL to detect and send mail via sp_send_dbmail (assuming you have it configured) or create an SSIS package that contains the logic and uses a Send Mail Task.

Now you have a tool that when it runs detects the overlong situation and sends notification.

The final step is how do you make it go. The cheap and lazy route would be to schedule a job every N intervals that fires off the solution above and checks. If you wanted more elegance, you could add a step in your existing SQL Agent job that schedules a one off execution of your job check logic, perhaps even a self-deleting job scheduled for a single execution, that runs 2:30 from now. If the package completes, then it will do nothing and delete. Otherwise, you get your page and have to jiggle the database. Harder to do it this way and uglier to test but there's a certain elegance to it.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.