problem executing DTS package on SQL Server 2008

Hello
I am attempting to run a DTS Package from a windows XP client

The package is held on SQL Server 2008 R2 Express database, the SQL server machine is Windows server 2008 R2 Standard 64-bit

The package was originally created on SQL Server 2000

On the server i have installed SQL Server 2000 DTS Designer Components and SQL Server 2005 Backward compatibility

There is only 1 instance of SQL server on this machine

I am able to run the DTS Designer and modify the package within SQL Server Management Studio Express

I connect to the Server as sa user and modify the package

In DTS Designer i am able to Execute the package and all steps complete successfully, with desired results

On the windows XP client i have the DTS Run command and its related .dll and .rll files
I have successfully registered these dlls
axscphst.dll
dtsffile.dll
dtspkg.dll
dtspump.dll

I can connect from the xp machine with sqlcmd, for example:

sqlcmd -Sserver_name -Usa -Ppassword

Open in new window


The command i am using from the client is

dtsrun /S server_name /U sa /P password /N package_name

Open in new window


The results i get are

DTSRun:  Loading...
DTSRun:  Executing...
DTSRun OnStart:  DTSStep_DTSExecuteSQLTask_1
DTSRun OnStart:  DTSStep_DTSDataPumpTask_3
DTSRun OnError:  DTSStep_DTSDataPumpTask_3, Error = -2147467259 (80004005)
   Error string:  [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
   Error source:  Microsoft OLE DB Provider for SQL Server
   Help file:
   Help context:  0

Error Detail Records:

Error:  -2147467259 (80004005); Provider Error:  17 (11)
   Error string:
   Error source:
   Help file:
   Help context:  0

DTSRun OnFinish:  DTSStep_DTSDataPumpTask_3
DTSRun OnError:  DTSStep_DTSExecuteSQLTask_1, Error = -2147467259 (80004005)
   Error string:  [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
   Error source:  Microsoft OLE DB Provider for SQL Server
   Help file:
   Help context:  0

Error Detail Records:

Error:  -2147467259 (80004005); Provider Error:  17 (11)
   Error string:
   Error source:
   Help file:
   Help context:  0

DTSRun OnFinish:  DTSStep_DTSExecuteSQLTask_1
DTSRun:  Package execution complete.

Open in new window


I dont know why it is giving me SQL Server does not exist.. error

The sa user has public and sysadmin roles and db_owner and public on the database i am trying to access

Any help or tips would be great thanks

Best regards

Patrick
hoganStand18092011Asked:
Who is Participating?
 
hoganStand18092011Connect With a Mentor Author Commented:
Hello
I have it sorted now, phew, a long journey ends...

The error above was because when i saved the data connection in the package i just accepted the default server name of (local)

This is a problem because the dtsrun executes on the client and cannot find a (local) server

So i entered the server_name in the package

I then had another error

Error string:  Error opening datafile: The system cannot find the path specified.

Open in new window


This is because i had specified the text files (destination files in the package) with the server file path D:\applications....

Again the client did not have this file path, so i changed the path to \\server_name\applications...

and now the package executes successfully

I had read all of that on many google searches but did not make the connection until now
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.