Link to home
Start Free TrialLog in
Avatar of bmgoa
bmgoa

asked on

DTS package not working

Hello,
     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?

Regards,
Bm
SOLUTION
Avatar of nmcdermaid
nmcdermaid

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of bmgoa
bmgoa

ASKER

Hello,
      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?

       
Avatar of bmgoa

ASKER

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.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial