we have a SQL Agent job which runs everyday in the morning at 4:00 am. This job hangs sometimes without providing any errors or warnings. It just hangs. BUT when i stop the job and run again it works perfectly. This happens once in a week at-least.
This job is executing a SSIS package which loads data from oracle (source) to SQL SERVER 2008 R2 (destination)
protection level for package is set to Encrypt Sensitive with password.
Steps Taken to Resolve issue
I thought the ssis package is losing connection in the middle of the process and i had increased the Connection Timeout=60 in the config file of this package but this doesn't worked. (I am using axml configuration file for connection string) I had configured logging for this package by using sysssislog table and i had checked this table when the job hanged, it doesn't showed any error information. I informed this issue to our network administrator to check is there any network issues, but he told that everything is fine with the network. In the OLEDB Destination editor i had used. Data Access Mode=Table or view-fastload Table lock=checked keep nulls=checked check constraints=checked rows per batch : blank The above steps didn't worked yet, and it continues hanging oftenly.
Please share your opinions on this issue. I need your advice to troubleshoot this issue
its worth checking whether there's any other job/process thats running parallely at sametime and causing interference. Put a profiler trace on server for the time and capture results onto a file. Then analyse the executed statements for any blocking issues
------------------------------------------------------------------------------------------------------ SQL Server MVP http://visakhm.blogspot.com/
sql agent job hangs without any errors or warnings
Posted - 01/18/2013 : 16:53:31
