DTS package "Index Not Found Error"

The Scenario is we have a development machine that is actually a virtual machine and we have a test server. Virtual machine is XP professional based machine and Test Server is Windows Server 2003 based machine.

We have created a DTS package in Sql Server 2000 which copies data from 7 foxpro .dbf files to Sql Server 2000 based Database. This package when we test at our Development machine(Virtual Machine) works very fine, but when same DTS package is executed at Test Server it gives an Error, Below is the error which we found in DTS Logs.

Package failed because Step 'DTSStep_DTSDataPumpTask_1' failed.
**************************************************************************
Step Error Source: Microsoft JET Database Engine
Step Error Description:Index not found.
Step Error code: 80040E21
Step Error Help File:
Step Error Help Context ID:5003015
***********************************************************************

All the tables are located at the same place. and in both the machine source path remains the same for DTS package.
One observation is that some time on test server DTS package copies data from 2 or 3 tables but fails for remaining and sometime it may fail for all.
Checks have been made to ensure that there is no access violation issue.

Looking forward for a quick Help

Thanks

Pranjal Sharma

One of the reason:

A Data Transformation Services (DTS) package that was created on a Microsoft Windows 2000 computer may not run when scheduled on a server without Microsoft Data Access Components (MDAC) version 2.5 installed. The same problem occurs if you run the package on the server with DTSRun

|||

But we have created the DTS package in Windows XP professional and not on Windows 2000 machine. One more interesting thing is that among 7 tasks few execute and few fail. I hope this would help you in thinking further about the query.

Thanks

Pranjal Sharma

|||You might need to check the MDAC version of server and on the machine where these DTS packages were created, also look at the DTS log for more information on this failure.|||

I tried checking the Versions. on the development machine MDAC version is 2.81 and on Test server it is 2.82. The log gives error which i provided in my first post.

But one thing i observed is that, If we give a select query which contains a Where clause, Then DTS package gives error and if we use select * from <tablename> then DTS is executed properly.

DTS help

I have not used SQL in a little while and am a little Rusty, and i have been stuck on this all day. Any advise would help. Here is the scenario

Two databases on two seperate servers: each database uses a different
user login

I am trying to pull out data in one field from one table in one database
and insert it into a row in another table.

Statements:

SELECT Count(*) as total_defects FROM server1.database1.dbo.defects
WHERE (idProduct = 31) AND (status <4)

(This statement works fine)

That statement returns the value to me correctly if I run it off of server1.

I then want to replace data in another table with that Count if it has a

certain product_id. Here is the logic for the statement I want to run.

Update local.sqatest.dbo.product SET ipro_defecttotal = total_defects
WHERE cpro_id = 00000001

Now i can't run it like the statement above, because I have two seperate

servers to log in to. Can I use some DTS method to do this?

Yes you can use DTS or SSIS. This forum is for SSIS-

Use a Data Flow with an OLE-DB Source, using your SELECT. That will generate one row which will flow down the pipeline. You can use a OLE-DB Command to perform the update.

Since it is only one row, you could also use an Execute SQL Task to run the select, and set the ResultSet to SingleRow. The result page can then be used to store the value into a variable. A second Execute SQL Task could then be used to run the UPDATE statement. Just use a parameter for the value, and map that to the variable.

The tasks mentioned and things like parameters are all documented in Books Online, so have a quick read of the relevant topics if you are not already familiar with concepts such as parameters in Execute SQL Tasks for example.

For DTS you can do the same thing, but for the Data Flow, use the Data Driven Query task as that allows parameterise statements such as UPDATEs. The Execute SQL Task method should work along much the same lines.