Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Modifying SQL Server 2000 DTS Packages Using Disconnected Edit


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

Attend these FREE MSSQLTips webcasts >> click to register


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:

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


Last Update:


signup button

next tip button



About the author
MSSQLTips author Edgewood Solutions Edgewood Solutions is a technology company focused on Microsoft SQL Server and founder of MSSQLTips.com.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



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

Learn more about SQL Server tools