Solved

problem executing DTS package on SQL Server 2008

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
hyperlink data type in SQL 3 25
What is the proper way to use for criteria in left join? 7 25
Help Required 3 92
Passing Parameter to Stored Procedure 4 23
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
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 the fundamental information of how to create a table.

785 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