DTS package not working

Posted on 2007-10-17
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 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
    LVL 30

    Assisted Solution

    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?
    LVL 8

    Assisted Solution

    Have you tried specifying the username and password in the dts run command and see if that works?


    Author Comment

          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

    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

    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

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Suggested Solutions

    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

    754 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

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now