Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

problem executing DTS package on SQL Server 2008

Posted on 2013-05-10
1
Medium Priority
?
1,478 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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

721 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