DTS Package hangs while running using SQL Server Agent Jobs

Question

Hi everyone

I have a DTS package which is scheduled to run every 15 minutes. Sometimes it hangs and we have to kill it manually. I have a Execute SQL Task which runs a batch file and Send Mail Task along with other tasks. What might be the possible casue for this? What are the parameters I have to look for?

Is that because the target DB is down at the time of run. Please give your suggestions. Let me know if you want more details.

Thanks in advance 

---

Sabi

Answer 1

You need to enable logging in your ssis package and then examine the logs to see where it is "hanging" .  For starters turn on all of the events.  Then when it hangs you can just open the log file while the process is hung and it'll show you where it is at.

---

Chuck

Answer 2

Hi everyone

I have a DTS package which is scheduled to run every 15 minutes. Sometimes it hangs and we have to kill it manually. I have a Execute SQL Task which runs a batch file and Send Mail Task along with other tasks. What might be the possible casue for this? What are the parameters I have to look for?

Is that because the target DB is down at the time of run. Please give your suggestions. Let me know if you want more details.

Thanks in advance 

---

Sabi

My guess is the batch file that is somehow sometimes not returning it's commit state.
I've heard about this before.

---

MCTS, MCITP - Please mark posts as answered where appropriate.

Answer 3

Hi Sabi,

There might be various reasons to cause a SSIS package hanged for running and generally for a hang issue, there is no log information helpful for diagnostics. These are the difficulties of troubleshooting hang issues. there are three common aspects to help you perform self-troubleshooting:
1. Resource bottleneck
2. Blocking or Deadlock
3. Poor performance query

For more information about it, please refer to:
How do I troubleshoot SSIS package hanged for running: http://social.msdn.microsoft.com/Forums/en/sqlintegrationservices/thread/2cb0bec7-0fe2-4b8d-b59e-9534388c72e7 

Thanks,
Eileen