Solved

problem executing DTS package on SQL Server 2008

Posted on 2013-05-10
1
1,425 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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

Suggested Solutions

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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…

733 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