Modifying SQL Server 2000 DTS Packages Using Disconnected Edit

By: Edgewood Solutions | Read Comments (1) | Print Edgewood Solutions is a technology company focused on Microsoft SQL Server and founder of MSSQLTips.com. Related Tips: More

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

Related Tips: More | Become a paid author

Last Update: 10/30/2006

Share:

Share

Tweet

Free SQL Server Learning Waiting on Waits Let's connect on facebook! Tweet...tweet...tweet... Follow us on twitter

Comments and Feedback:

Wednesday, February 15, 2012 - 8:01:38 AM - Anandarajeshwaran.J	Read The Tip
you saved my day mate

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code

 

Sponsor Information

"Amazing, Amazing, Amazing! SQL doctor is truly one of the most powerful tools I have seen." SQL Monitor, server monitoring so easy, your boss could do it. Try it online. Need SQL Server help and not sure where to turn? Reach out to expert consultants for a Health Check. Find and Fix SQL issues with Foglight Performance Analysis. Get a free copy. Solving SQL Server problems for millions of DBAs and Devs since 2006. Join now. Learn SQL Server 2012, Performance Tuning, Development, Administration, Replication and more - free webcasts