Modifying SQL Server 2000 DTS Packages Using Disconnected Edit

By: MSSQL Tips   |   Updated: 2006-10-30   |   Comments (1)   |   Related: More > Data Transformation Services

Problem

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.

Solution

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:

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:

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:
  
  
• 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:
  
  
  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:
  
  
• 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:
  
  

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

About the author

MSSQLTips.com 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