January 18, 2013 at 2:51 pm
Problem:
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 atleast.
This job is executing a SSIS package which loads data from oracle (source) to SQL SERVER 2008R2 (destination)
Steps Taken to Resolve issue
1) 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 a xml configuration file for connection string }
2) 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.
3) 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.
4) 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
January 18, 2013 at 3:51 pm
You could do a couple of things:
1. Run Profiler and trace the job.
2. Try running the steps from SSMS (if possible).
3. Create a "log" table and insert into it after every statement. This shows you on which statement the job is stuck on.
My guess is that one of the statements in your job is being blocked.
January 20, 2013 at 7:50 am
Are there any Script Tasks with non-trivial programs in them? Look for code that produces a message box. Sometimes developers will leave one in there by accident and it will only execute under specific circumstances which makes the hanging-behavior seem quite random.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply