February 25, 2009 at 7:46 am
I have a SSIS package that ran in production last week. The first two times it failed in different steps due to Timeout issues, but ran successfully two other times.
I'm pretty sure it's related to server load, but I'm trying to modify the package to avoid the timeouts in the future. It's timing out on separate exec sql tasks. Looking at the individual tasks, I see a field called TimeOut. One task is set to '10' and another is set to '30'. What are good numbers to have in here? I thought '0' was unlimited, but when I try to change the value to that, I get a message 'Property value is not valid'
Thanks,
Curt
February 25, 2009 at 8:00 am
According to BOL 0 is a valid value for the timeout property and is the default value. Here is a quote from BOL:
TimeOut
Specify the maximum number of seconds the task will run before timing out. A value of 0 indicates an infinite time. The default is 0.
Note:
Stored procedures do not time out if they emulate sleep functionality by providing time for connections to be made and transactions to complete that is greater than the number of seconds specified by TimeOut. However, stored procedures that execute queries are always subject to the time restriction specified by TimeOut.
What service pack level are you on?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 25, 2009 at 8:14 am
I'm running version 9.00.1399.00
February 25, 2009 at 8:22 am
According to this article[/url] that is the RTM version. You may want to start looking at service packs since there are 3 available and this may have been a bug that was fixed in one of them. I am running SP3 on my desktop where I do SSIS dev and do not have this issue.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 25, 2009 at 8:30 am
I'm a consultant at a client site, so I don't have the authority or capability to upgrade the version I'm running. Any other suggestions?
February 25, 2009 at 8:39 am
I would definitely recommend to the client that they start preparing to apply the service packs as there are several security related issues that are addressed by the service packs. They should at least be at SP2 and be looking at SP3.
For you specific situation the best option is to check the execution plans for the queries being run by the execute sql tasks and optimize them. You can either run the queries in SSMS with show estimated execution plan or show actual execution plan. Or you may be able to find them in the DMV's. Here's a blog post that has a query that shows which DMV's to use to get to the execution plan, http://scarydba.wordpress.com/2009/02/18/refining-the-missing-index-data/. It is not exactly what you want but points you in the right direction. Or you could take the easy way out and crank up the time out property.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply