Solved

problem executing DTS package on SQL Server 2008

Posted on 2013-05-10
1
1,442 Views
Last Modified: 2013-05-10
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
0
Comment
Question by:hoganStand18092011
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 

Accepted Solution

by:
hoganStand18092011 earned 0 total points
ID: 39157051
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question