Hi,

I have a number of clients who all use SQL Server 2000 and are running my
product.
One of the clients is having a problem running one of the DTS Packages from
my Windows
application.

The Windows app calls a stored procedure on the SQL DB that uses xp_cmdshell
which in turn runs the DTSRun utility.

All the DTS package does is import data from an Excel file into a holding
table in the DB.
Because the DTS Package has to work with a "Live" and a "Test" copy of the
database
I pass in the name of the database to use for connections and store it a
global variable
which is then mapped to the appropriate property on the connection task.
Likewise
with the full path of the Excel file.

When a user runs the import they get the totally unhelpful "unspecified
error" message,
the source being the Microsoft Jet Engine.

Okay now for some server config stuff. The SQL Server service is run with a
specific
NT domain account, the SQL Agent proxy account has been set to an NT domain
account.

Both of these accounts have been give full control permissions over the
share directory
where the Excel file is located. Both the users have virtually administrator
rights.
The policy settings on the server have been set such that these users can
act as part of
the operating system, replace process level token, log on as a batch job and
increase
user quotas (aka 'adjust memory quotas for a process'). Both users also have
full
control on the servers temp directories. Even with all these updated
permissions
it still fails with the same error mentioned above. I have remotely logged
in myself
and then logged into their system using the same account as the SQL Agent
proxy
and still it doesn't work.

However....
If I use Enterprise Manager, go into the design of the DTS Package in
question,
set the global variables that would normal be passed in to appropriate
values and
run it in design mode then it completes successfully.

Moreover if I take a back-up of their database and then restore it on the
SQL server
in my office AND use the same Excel file they are trying to import and then
use my
app to import it then it works absolutely fine.

In addition I have tried reinstalling the DTS Package in case it got
corrupted but still
the same error.

The only difference between my SQL Server and theirs is that mine is running
Developer
Editon SP3 and theirs is running on a clustered SQL Server SP4 (not sure
which version
but we tell them standard is minimum), both use Win 2K3.

I have other clients using the same software running on a SP4 SQL Server and
I've
tested it on another server in the office that has SP4 on so I don't think
the SP difference
is the cause.

If anybody has any ideas as to how I might solve the problem or how I might
debug
it further then please help me.

Regards,
Peter

Peter Row wrote:

Did you try turning on logging for the DTS package? That may give you better error information than just "unspecified
error"