DTS Multi Step Error

[bsal81]

Hey Everyone,

I've got a problem with a SQL7 DTS Package that seems to be failing on about three machines out of 300. The DTS package itself is responsible for transferring data from SQL7 to an Access DB. I turned on error logging for both the DTS Package and each of the DataPump Tasks. I was able to capture the following from the error log generated by the DTS package.

--

The execution of the following DTS Package failed:

Error Source: Microsoft Data Transformation Services (DTS) Package
Error Descriptionackage failed because Step 'DTSStep_DTSDataPumpTask_1' failed.
Error code: 80040428
\Error Help File:sqldts.hlp
Error Help Context ID:0


Package Name: Export
Package Description: Exports Information to the Access Database
Package ID: {7BBC245D-C2EB-45E9-9465-386B129D6CBC}
Package Version: {C78A6FB0-A44F-4096-A2AF-E229FAB3DFD0}
Package Execution Lineage: {E934D51C-089A-4B3A-9894-4100442B65BF}
Executed On: DW579020
Executed By: user1
Execution Started: 7/23/2007 3:01:16 PM
Execution Completed: 7/23/2007 3:01:16 PM

Package Steps execution information:

Step 'DTSStep_DTSDataPumpTask_1' failed

Step Error Source: Microsoft OLE DB Provider for SQL Server
Step Error Description:Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
Step Error code: 80040E21
Step Error Help File:
Step Error Help Context ID:0

Step 'DTSStep_DTSDataPumpTask_2' was not executed
Step 'DTSStep_DTSDataPumpTask_3' was not executed
Step 'DTSStep_DTSDataPumpTask_4' was not executed
Step 'DTSStep_DTSDataPumpTask_5' was not executed
Step 'DTSStep_DTSDataPumpTask_6' was not executed
Step 'DTSStep_DTSDataPumpTask_7' was not executed

---

I've checked the access database and all of the field lengths seem to be OK. I also thought about a permissions problem since the DataPumpTask_1 didn't generate an error log. But have been unable to replicate the error in a test environment while tinkering with permissions. Any Ideas?

Thanks for your help

Brent

 

[bsal81]

Nobody has any ideas on this?

 

[AlexCuse]

How are you executing the package from these 300 machines?

And where is the access database that you are inserting to? Is it a fixed location?

I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom

 

[bsal81]

Thanks for your reply, I'm executing these DTS packages from within my VB6 application and the access db is on the local machine in a fixed location.

I just figured out what was causing the problem, though. The three problem machines were running windows 2000 sp4 and apparently did not have the MDAC 2.7 SP1 installed. Installing this SP corrected the issue. I guess somewhere along the way maybe these DTS packages got built on a machine with this service pack and caused the dependency.