strange DTS behaviour?????????

Question

strange DTS behaviour?????????

Papillon

Hall of Fame

Points: 3303
More actions
December 20, 2005 at 11:07 pm

#76465

Hi...
We have some DTS packages here scheduled to run..Sometimes we got error that job failed. When i checked it in event viewer i got message like.....
Step 'DTSStep_DTSExecuteSQLTask_16' succeeded
Step Execution Started: 12/20/2005 5:43:35 AM
Step Execution Completed: 12/20/2005 5:43:42 AM
Total Step Execution Time: 6.672 seconds
Progress count in Step: 0
Step 'DTSStep_DTSExecuteSQLTask_17' failed
Step Error Source: Microsoft OLE DB Provider for SQL Server
Step Error Description:Timeout expired
Step Error code: 80040E31
Step Error Help File:
Step Error Help Context ID:0
Step Execution Started: 12/20/2005 5:48:31 AM
Step Execution Completed: 12/20/2005 8:35:13 AM
Total Step Execution Time: 10001.61 seconds
Progress count in Step: 0
Step 'DTSStep_DTSExecuteSQLTask_18' succeeded
Step Execution Started: 12/20/2005 5:30:02 AM
Step Execution Completed: 12/20/2005 5:30:16 AM
Total Step Execution Time: 14.14 seconds
Progress count in Step: 0
Step 'DTSStep_DTSExecuteSQLTask_19' succeeded
Step Execution Started: 12/20/2005 5:43:53 AM
Step Execution Completed: 12/20/2005 5:44:48 AM
Total Step Execution Time: 55.656 seconds
Progress count in Step: 0
What may be reason cause this failure and another thing is that see failure occure at 'DTSStep_DTSExecuteSQLTask_17' and see the time for started and completed..after that step no. 18 and 19 executed and see their time...how that was possible before completion of this step 17 how step 18 nad 19 completed????..I chked in log also same thing i observed that 'DTSStep_DTSExecuteSQLTask_1,'DTSStep_DTSExecuteSQLTask_2,
'DTSStep_DTSExecuteSQLTask_6' (actually it shoud be 'DTSStep_DTSExecuteSQLTask_3..????) wheather these number 1,2,6,..like that17,18,19 is logically sequenced with actual runniing step??? please guide me what u observed in above confusing senario......pls help me out ..its urgent...
Regards,
Papillon

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Greg Charles SSC-Forever Points: 45403 More actions · Answer 1

Papillon,

The there are lots of causes for a timeout error, especially if it's intermittant. There could be another process blocking the Execute SQL task or the timeout property might be set too low for the connection used by the task. Check the ConnectionTimeout property for the connection in Disconnected Edit. Is the package moving a lot of data? The step that timed out ran for almost 3 hours!

As for the steps that seem to be out of sequence, they will run in parallel unless forced to run serially by the use of workflow. If you want them to run in a specific sequence, connect each task with workflow that specifies the previous task as a precedent.

Greg

Papillon Hall of Fame Points: 3303 More actions · Answer 2

Hi!!

Greg!! yes i got the reason why it is failed and due to which process..by observing properties from Disconnected Edit...thanx for ur valuable time....

Now as u said "As for the steps that seem to be out of sequence, they will run in parallel unless forced to run serially by the use of workflow" yes some task are running parallel with the DTS runnning...But how to connect such task in serially????..and why DTS assign by defalut randomly naming all the task as in my case...serials are ...

'DTSStep_DTSExecuteSQLTask_1,

'DTSStep_DTSExecuteSQLTask_2,

'DTSStep_DTSExecuteSQLTask_6' (actually it shoud be 'DTSStep_DTSExecuteSQLTask_3..????)

'DTSStep_DTSExecuteSQLTask_7,

'DTSStep_DTSExecuteSQLTask_8,

'DTSStep_DTSExecuteSQLTask_3, (????????)

'DTSStep_DTSExecuteSQLTask_9,..... like that!!!!!

again thanx Greg.....

Regards,

Papillon

Bryant McClellan SSCarpal Tunnel Points: 4337 More actions · Answer 3

My experience with DTS suggests that the task numbering has to do with the order of creation. If you have 10 tasks (created in order 1-10) with workflow that executes them in order, create a new one and insert between 1 and 2, now the task execution is 1-11-2-3...10. If you create and delete a number of tasks enroute to the fnished product, the numbers lose meaning.

Just my observationhere, but am I correct?

------------
Buy the ticket, take the ride. -- Hunter S. Thompson

Greg Charles SSC-Forever Points: 45403 More actions · Answer 4

You're absolutely correct. The tasks (and the steps that use them) are named in the order they are created, not the order they are eventually executed. Unfortunately, you can't make up you own names when you create the tasks. You can only rename them in disconnected edit after creation.

I'll admit that I don't bother with renaming the tasks and steps. If I'm trying to figure out what a particular step does, I look in disconnected edit.

Greg