SQL DTS Error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

SQL DTS Error

Author

Topic

sqlserverdeveloper
Posting Yak Master

243 Posts

Posted - 2007-12-18 : 12:02:08

I have about 12 DTS packages inside 1 dts package and it's been running fine for about 2 weeks now and I got this error on one dts package this morning. I googled the error and found the below link:
http://support.microsoft.com/kb/319058
But I am not using any VB code in running this DTS.
I am running this DTS as a scheduled sql job where I am using DTSRUN ...oper sys command.
Any help is greatly appreciated, thanks!!
Below is part of the error:
Event Type: Error
Event Source: DataTransformationServices
Event Category: None
Event ID: 81
Date: 12/18/2007
Time: 11:10:11 AM
User: N/A
Computer: Name
Description:
The execution of the following DTS Package failed:
Error Source: Microsoft Data Transformation Services (DTS) Package
Error Description:Execution was canceled by user.
Error code: 80040427
\Error Help File:sqldts80.hlp
Error Help Context ID:10100

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-12-18 : 16:06:39

This happens when the job is cancelled during it's execution. You must have another process that was scheduled which terminated or cancelled open spids.

Poor planning on your part does not constitute an emergency on my part.

sqlserverdeveloper
Posting Yak Master

243 Posts

Posted - 2007-12-18 : 16:37:51

quote:
Originally posted by dataguru1971

This happens when the job is cancelled during it's execution. You must have another process that was scheduled which terminated or cancelled open spids.

Poor planning on your part does not constitute an emergency on my part.

No it was not cancelled while it was getting executed, there was nothing running at that time.
Here is the error from the error log:
The execution of the following DTS Package failed:

Error Source: Microsoft Data Transformation Services (DTS) Package
Error Description:Execution was canceled by user.
Error code: 80040427
\Error Help File:sqldts80.hlp
Error Help Context ID:10100

Package Name: DTSPackage
Package Description: DTS package description
Package ID: {645722E4-C653-48AE-9EB4-9188DD20A599}
Package Version: {2DEEC696-E63F-45B1-8F72-F577E8D23437}
Package Execution Lineage: {BD8FEDC9-3C30-454E-A68A-AC2B99E999CA}
Executed On: servername
Executed By: username
Execution Started: 12/18/2007 11:10:10 AM
Execution Completed: 12/18/2007 11:10:11 AM
Total Execution Time: 0.812 seconds

Package Steps execution information:

Step 'Create Table tablename Step' succeeded
Step Execution Started: 12/18/2007 11:10:10 AM
Step Execution Completed: 12/18/2007 11:10:10 AM
Total Step Execution Time: 0.078 seconds
Progress count in Step: 0

Step 'Copy Data from file to tablename Step' succeeded
Step Execution Started: 12/18/2007 11:10:10 AM
Step Execution Completed: 12/18/2007 11:10:11 AM
Total Step Execution Time: 0.172 seconds
Progress count in Step: 84

Step 'DTSStep_DTSExecuteSQLTask_1' succeeded
Step Execution Started: 12/18/2007 11:10:10 AM
Step Execution Completed: 12/18/2007 11:10:10 AM
Total Step Execution Time: 0.125 seconds
Progress count in Step: 0

Step 'DTSStep_DTSExecuteSQLTask_2' was not executed

Step 'DTSStep_DTSExecuteSQLTask_3' was not executed

Step 'DTSStep_SDN.SMTPTask_1' was not executed

Step 'DTSStep_SDN.SMTPTask_2' succeeded
Step Execution Started: 12/18/2007 11:10:10 AM
Step Execution Completed: 12/18/2007 11:10:10 AM
Total Step Execution Time: 0.187 seconds
Progress count in Step: 0
Task Log for this step:
-----Start Task Log-----

Error = 1 (00000001), Description = DTS: Log initialized
Error = 1 (00000001), Description = DTS: Log closed

-----End Task Log-----

Step 'DTSStep_SDN.SMTPTask_3' was not executed

Step 'DTSStep_SDN.SMTPTask_4' succeeded
Step Execution Started: 12/18/2007 11:10:10 AM
Step Execution Completed: 12/18/2007 11:10:10 AM
Total Step Execution Time: 0.219 seconds
Progress count in Step: 0
Task Log for this step:
-----Start Task Log-----

Error = 1 (00000001), Description = DTS: Log initialized
Error = 1 (00000001), Description = DTS: Log closed

-----End Task Log-----

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-12-18 : 22:11:54

What do 'DTSStep_DTSExecuteSQLTask_2' and other failed steps do?

sqlserverdeveloper
Posting Yak Master

243 Posts

Posted - 2007-12-20 : 12:46:51

Step 'DTSStep_DTSExecuteSQLTask_2': Creates indexes on the table
Step 'DTSStep_DTSExecuteSQLTask_3': Inserts timestamp in a table with the package name, just for my info.
Step 'DTSStep_SDN.SMTPTask_1': Using SMTP mail task for emailing that the DTS is done.
Thanks.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-12-20 : 16:03:05

Try trace the job in profiler to find out why they failed to run.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources