Modifying SQL Server 2000 DTS Packages Using Disconnected Edit

By:   |   Comments (1)   |   Related: More > Data Transformation Services


During the creation of Data Transformation Services (DTS) packages, DTS checks to see if the objects that are referred to actually exist (i.e., connections). If the package is created on one server, but deployed on another, then some modification is necessary, otherwise the package will fail. There may also be times when you want to transfer DTS packages stored as SQL Server packages or perform other tasks using system tables. These tables do not appear in the source and destination lists of Transform Data Tasks, so you must change the table name manually. One method you can use to make corrections or access database objects not available through traditional means is the Disconnected Edit method.


The Disconnected Edit process bypasses the validity checking of DTS objects so that changes can be made to the package.Once a package is created you can right-click an empty area on the Designer surface and choose Disconnected Edit:

Choosing the Disconnected Edit method

As you can see, this gives you access to every connection, task, and step in the package. These objects are arranged in a tree view that allows you to navigate to the particular object property that needs modification:

The main view of the Disconnected Edit screen

Here are a few examples of times when you should use Disconnected Edit:

  • If the package is moved from one server to the other and the Data Source needs to be modified. In this case navigate through the OLE DB properties and click on Data Source. Then double-click Value. From here you can change the data type of the value as well as the value itself:
    Modifying the Data Source of a Database Connection

  • A common task performed by administrators is moving DTS packages from one server to another. Since certain system tables (like msdb.dbo.sysdtspackages) don't appear in the source or destination object list of a Transform Data Task, they must be entered manually:
    List of objects available for source/destination in Transform Data Task

    In order to manually edit the source and destination objects, click directly on the Data Pump task. You will see a list of properties on the right. Double- click the Destination Object Name and Source Object name and enter "msdb.dbo.sysdtspackages" for each, since that is where DTS packages are stored in SQL Server. If you want to move only certain packages, you can narrow the information to be copied using a SQL Query:
    Properties of the Data Pump Task

  • A table name has changed and you reference that table in an Execute SQL Task in a package. Click directly on the Execute SQL Task, double-click the SQLStatement and edit the statement in the value textbox:
    Modifying the SQL Statement in the Execute SQL Task

A word of caution about using Disconnected Edit: this should be used very cautiously. In the DTS Designer the package checks for the presence of objects as you create the package. The same benefit is not present when using Disconnected Edit, so you may enter erroneous information without warning.

Next Steps
  • Make your packages more mobile and take advantage of DTS features by using Disconnected Edit
  • Become familiar with different properties of DTS objects by selecting Disconnected Edit and navigating through the objects
  • Go to MSSQLTIPS for additional information on Data Transformation Services

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author MSSQLTips was started in 2006 to provide SQL Server content about various aspects of SQL Server and other database platforms.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Wednesday, February 15, 2012 - 8:01:38 AM - Anandarajeshwaran.J Back To Top (16024)
you saved my day mate

get free sql tips
agree to terms