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.

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

share|improve this question
    
Do you call of xp_cmdshell in your job? What is the wait type in your ssis package? The links below might be helpful: –  Nazila Beikpour Jan 20 '13 at 0:48
1  
Sorry, I was typing the comments above from my mobile and made mistakes, which the forum did not let me to delete or edit them after 5 minutes. Do you have call of xp_cmdshell in your job? Is it possible that you are trying to use a resource that is not available at time of running Or the user you have set up to run the job (scheduled) does not have the required rights to access the resource? Also what is the wait type in your ssis package? Is it PREEMPTIVE_OS_PIPEOPS? If so, please check the link below: sqlservercentral.com/Forums/Topic802987-110-1.aspx#bm1339542 The links below al –  Nazila Beikpour Jan 20 '13 at 1:06
1  
What's the wait type of the running process in the SQL Server? Is the query from the SSIS package being executed against the Oracle server when it "hangs"? –  mrdenny Jan 21 '13 at 19:19
    
hi nazila!! we don't have call to xp_cmdshell in our job. I guess "it is possible that i am are trying to use a resource that is not available at time of running". The user set up to run the job have all the required rights(Administrator). I have to check wait type. Thanks for your time –  oOo Testing1 oOo Jan 22 '13 at 1:49
    
hi mrdenny!! the query is executed against oracle server when it hangs!! I didn't know about wait types... i will check wait types now when it hangs... thanks –  oOo Testing1 oOo Jan 22 '13 at 1:56
add comment

Your Answer

 
discard

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

Browse other questions tagged or ask your own question.