DTS package not working

Posted on 2007-10-17
Medium Priority
Last Modified: 2013-11-30
     I recently upgraded my SQl 2000 to SQl 2005 sp2.
     Microsoft says existing DTS packages would continue to run under 2005 with out changes.
     However my sql given below which was working before is no longer working.

declare      @Cmd                  varchar (200)
select      @Cmd = 'dtsrun /S' + @@servername + ' /E /WTrue /NDTS_Extract_Files'
exec      master..xp_cmdshell @Cmd

It does not show any error.so what is going wrong?

I downloaded the Microsoft Sql Server 2005 DTS Designer and opend my package in it. Right Click -> Execute..
It is working fine.

The above query when i run from MS Sql Server Management Studion query window, it is not working.

Any ideas?

Question by:bmgoa
  • 2
  • 2
LVL 30

Assisted Solution

nmcdermaid earned 750 total points
ID: 20099491
Run the command line in a command prompt... does it show any error?

What does the DTS log say? does it say that it ran?

Assisted Solution

matrix_aash earned 300 total points
ID: 20099753
Have you tried specifying the username and password in the dts run command and see if that works?


Author Comment

ID: 20100115
      I think i figured it out:
     actually a copy of DTSRun was lying in C:\Windows\System32 without the other requisites dll.
     So the command was not working fine; I deleted the copy from System 32. and qualified the full path in the command i.e.
select      @Cmd = '"C:\Program Files\Microsoft SQL Server\80\Tools\Binn\DTSRun" /S' + @@servername + ' /E /WTrue /NDTS_Extract_Files'
  it worked now.
     Actually  C:\Program Files\Microsoft SQL Server\80\Tools\Binn path is present in the system path variable; so don't know why Studio is not taking it. Any idea on this?


Author Comment

ID: 20100354
i ran the Path command using xp_cmd shell in studio's query window.
It seems to truncate the Path and the value  C:\Program Files\Microsoft SQL Server\80\Tools\Binn  is not coming in. it shows upto 1031 characters and the above path is beyond that range.
don't know why it truncates though.
LVL 30

Accepted Solution

nmcdermaid earned 750 total points
ID: 20107696
I don't know why it isn't searching the path. Maybe you need log in as the SQL Server service and set the path there.

You could also try executing SET PATH=%PATH%;C:\Program Files\Microsoft SQL Server\80\Tools\Binn

beforehand (talk to the DOS guys for the correct syntax)


Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

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

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

621 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