DTS package execution never reaches completion!

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

Import/Export (DTS) and Replication (2000)

DTS package execution never reaches completion!

Author

Topic

NickStan
Starting Member

36 Posts

Posted - 2007-06-20 : 06:24:41

I am running SQL server 8.0 on Microsoft Windows Server 2003, Service Pack 2.

The problem that I am having with some of my DTS packages is that they never finish executing. I know that they get to the final step in the package because that step is to email the file out and I receive the email and the file attached generated correctly.

I am using DTSRun in my jobs to execute the DTS package.
Like this: DTSRUN /SUK-SERVER /NCJFR:DailyReport /E

If I look in the Event Viewer -> System I can see the following error message:

"Application popup: DTSRUN.exe - Application Error : The instruction at "0x7c82a754" referenced memory at "0xffffffff". The memory could not be "read".
Click on OK to terminate the program"

Everyday I need to manually cancel the job else they will not start executing the next day.

Does anyone know how to solve this problem?

Thanks

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-06-20 : 11:29:58

What does that step do?

nr
SQLTeam MVY

12543 Posts

Posted - 2007-06-20 : 11:52:46

My guess would be that the email is causing problems.

Try turning on logging in the packages and see what happens. It'll log the start and end of each step so you can see where it's hanging.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

NickStan
Starting Member

36 Posts

Posted - 2007-06-21 : 07:16:32

The steps in the DTS package are:

1) Create and assign global variables.
2) Truncate SQL table that will hold the data
3) Select the data from Oracle into a SQL table using ODBC link
4) Select the data from the SQL table into the XML data file.
5) Add the appropriate header and footer to the XML data file using a batch file.
6) Email the file to the relevant person using xp_sendmail

I switched on logging a couple of days ago for another one of my DTS packages that has the same problem.

****************************************************************************************************
The execution of the following DTS Package succeeded:

Package Name: dailyDetail
Package Description: (null)
Package ID: {7B5264C2-3B15-4CB6-98FC-EF73C1AF6D6A}
Package Version: {509A3F6F-2E4C-494C-BF54-A1DBC88726ED}
Package Execution Lineage: {0BD45BBB-94BE-4456-B6B6-1375187EB441}
Executed On: UK-SERVER
Executed By: sql
Execution Started: 21/06/2007 08:30:01
Execution Completed: 21/06/2007 08:30:11
Total Execution Time: 9.938 seconds

Package Steps execution information:

Step 'DTSStep_DTSCreateProcessTask_2' succeeded
Step Execution Started: 21/06/2007 08:30:01
Step Execution Completed: 21/06/2007 08:30:01
Total Step Execution Time: 0.109 seconds
Progress count in Step: 0

Step 'DTSStep_DTSExecuteSQLTask_1' succeeded
Step Execution Started: 21/06/2007 08:30:01
Step Execution Completed: 21/06/2007 08:30:01
Total Step Execution Time: 0.079 seconds
Progress count in Step: 0

Step 'DTSStep_DTSDataPumpTask_1' succeeded
Step Execution Started: 21/06/2007 08:30:01
Step Execution Completed: 21/06/2007 08:30:01
Total Step Execution Time: 0.063 seconds
Progress count in Step: 27

Step 'DTSStep_DTSDataPumpTask_2' succeeded
Step Execution Started: 21/06/2007 08:30:01
Step Execution Completed: 21/06/2007 08:30:08
Total Step Execution Time: 6.984 seconds
Progress count in Step: 19

Step 'DTSStep_DTSDynamicPropertiesTask_1' succeeded
Step Execution Started: 21/06/2007 08:30:01
Step Execution Completed: 21/06/2007 08:30:01
Total Step Execution Time: 0 seconds
Progress count in Step: 0

Step 'DTSStep_DTSExecuteSQLTask_2' succeeded
Step Execution Started: 21/06/2007 08:30:01
Step Execution Completed: 21/06/2007 08:30:10
Total Step Execution Time: 9.297 seconds
Progress count in Step: 0
****************************************************************************************************

nr
SQLTeam MVY

12543 Posts

Posted - 2007-06-21 : 07:25:33

Have you tried scheduling a package that just does an email?

I try not to include emailing as part of a process. I would put the emails into a table and have another scheduled task that sends them.

I take it you have sp4 on the server?

Other things to try - add another step after the package and see if that ever gets executed.
If it doesn't split the package up into seperate packages for each task and put them in different steps.

(The xml bit could also be causing the problem).

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

NickStan
Starting Member

36 Posts

Posted - 2007-06-21 : 07:54:57

We are using Service Pack 2.

I created a package that does just an email, scheduled it as a job, it runs and completes successfully.

I put an extra step after the email task that does an insert into a table and it worked, however the job still appears as if it is executing.

Thanks

nr
SQLTeam MVY

12543 Posts

Posted - 2007-06-21 : 10:18:10

Interesting.
Can you apply sp4 - a lot of bugs have been fixed.
The error you are getting sounds like the app is accessing memory it shouldn't which can screw up anything.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Mrka
Starting Member

5 Posts

Posted - 2009-04-14 : 18:44:25

Did you ever find any solution for this? I have the same problem now...
Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources