Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2013
    Location
    Dublin, Ireland
    Posts
    11

    Unanswered: Locate DTS package SQL Server 2005

    Hi
    The system is SQL Server 2005
    (Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Express Edition on Windows NT 6.1 (Build 7600: )
    )
    on Windows SBS2011 64bit machine

    The database was upgraded from SQL Server 2000 recently (not by me, so at the moment I do not know the process that was used for the upgrade)

    On SQL Server 2000 it had some DTS packages

    My question is: Would the DTS packages be carried over to the new server on upgrade?

    If so, how do I know that they are present?

    (The system does not have the Microsoft SQL Server 2000 DTS Designer Components installed at the moment)

    I was thinking this query might tell me
    Code:
    SELECT * FROM sysdtspackages90
    Any help would be great, thanks

    Best regards

    Patrick

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    In SSMS: Management > Legacy > Data Transformation Services
    George
    Home | Blog

  3. #3
    Join Date
    Mar 2013
    Location
    Dublin, Ireland
    Posts
    11
    Thanks George, i will be at the site tomorrow and will look at it then

    In my own installation (my own PC) i have SSMS installed but cannot see that Node under Management

    Is it only visible if the DTS package is present

    Patrick

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I don't believe that it is visible at all if you do a "fresh install" of SQL 2005, but I don't have a copy handy to verify that. When you install the DTS / Legacy support (an add-on you can download), then it should appear. You can find a number of useful links here.

    If you upgrade a server in place to SQL 2005, then you ought to see the exisiting DTS packages there.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Mar 2013
    Location
    Dublin, Ireland
    Posts
    11
    Thanks Pat. The links are very useful: gradually my brain is engaging in the process..

    I think they installed SQL 2005 and then somehow attached or restored the SQL 2000 db

    I am hopeful that when i install the DTS support i will see the package

  6. #6
    Join Date
    Mar 2013
    Location
    Dublin, Ireland
    Posts
    11
    Hi

    I was able to run enterprise manager from the SQL 2000 system and then Save As the package to the new SQL 2005 system

    (I found these links useful
    How to copy DTS 2000 packages between servers (and from SQL 2000 to SQL 2005 and SQL 2008) - CSS SQL Server Engineers - Site Home - MSDN Blogs

    and

    Migrating Data Transformation Services Packages
    )

    When i run this query on SQL 2005 i can see the package listed on the table

    Code:
    SELECT * FROM sysdtspackages
    But i have nothing under the Management node except SQL Server Logs and the Activity Monitor

    I have installed the SQL Server DTS Designer Components (SQLServer2005_DTS.msi)

    Is there anything else i need to do

    Best regards

    Patrick

  7. #7
    Join Date
    Mar 2013
    Location
    Dublin, Ireland
    Posts
    11
    I finally sorted this out...

    The SQL 2000 server was still running on the network so i was able to connect to it and modify the package in Enterprise Manager to suit the new SQL server 2005 db

    I then Saved As the package and was able to save it into the new SQL server db

    The package was then executed from the client using DTSRUN command
    Code:
    dtsrun /S SQLServer2005\SQLServerExpress /U sa /P password /N package_name_on_2005_db
    The client is Win 7 pc

    The package ran fine, each step executed successfully

    The package extracted data from the db into csv files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •