Enterprise Manager and DTS Packages

The Enterprise Manager can be used to view, modify, schedule, and execute DTS packages stored in SQL Server or Meta Data Services repository. Enterprise Manager can also open DTS packages saved as structured storage files. A Data Transformation Services folder is located in the left pane tree view of Enterprise Manager below each instance of SQL Server. Right-clicking the mouse on the Data Transformation Services folder provides options to create a new package or open an existing package for modification. The Create Package option starts the DTS Import/Export Wizard to create a new DTS package. The Open Package option allows you to open an existing DTS package file that was saved as a structured storage file and start the DTS Designer for making changes to the package. The DTS Designer provides a Save As option from the Package menu that can be used to save a DTS package in any of the DTS formats and locations.

Below the Data Transformation Services folder are separate folders for Local Packages and Meta Data Services Packages stored in SQL Server. Navigating to one of these folders and right-clicking an existing DTS package provides options to edit the package using the DTS Designer (called Package Design in the Windows title bar), schedule the package for later execution, or execute the package immediately.

While the DTS Import/Export Wizard simplifies the complex process of initially creating a DTS package, the DTS Designer used to modify an existing package is more challenging. The DTS Import/Export Wizard is a high-level tool that abstracts the DTS package creation process. In contrast, the DTS Designer is a very low-level tool that requires interaction with the DTS package at a very detailed level.

The Disconnected Edit option from the Package menu of the DTS Designer provides a two-pane control with a tree view in the left pane for displaying and editing the individual DTS objects and properties. The DTS objects include Connections, Tasks, Steps, and Global Variables defined in the DTS package. Each DTS object can have a large number of individual properties. For example, OLE DB Properties under the Connection object include about 80 individual properties that may be edited. Each DTS property has a minimum set of attributes that include Name, PropertyID, PropertySet (GUID), Value, and IsDefaultValue (a Boolean value indicating if the attribute value is the default). Because the granularity is so detailed in the DTS Designer, it is often easier to recreate a DTS package using the DTS Import/Export Wizard if a large number of changes are required.

Previous Next