Data Transformation Services in SQL Server 2000 (DTS)

server| Data | conversion

Absrtact: To accomplish the tasks of data merging, archiving and analysis, for application development, database administrators often need to import, export, and transform data for database or server upgrades. Data Transformation Services (DTS) in SQL Server 2000 provides a set of graphical tools and programmable objects to help administrators and developers solve data transfer issues from different sources to single or multiple destinations, including data extraction, transformation, and merging. You can make a DTS package of tasks, workflow actions, and constraints, and then schedule the packets to execute periodically or when a particular event occurs. This white paper describes DTS, gives you some components and services that you can use to create DTS solutions, describes how to implement DTS solutions using DTS Designer, and finally describes DTS application development.

Content of this page
Introduction to DTS
Using DTS Designer
Options for saving DTS packets
Use DTS as an application development platform

Introduction to DTS
Most companies have a variety of storage formats and storage locations for their data. To support decision making, improve system performance, or upgrade existing systems, it is often necessary to move data from one data storage location to another.

Microsoft SQL Server 2000 Data Transformation Services (DTS) provides a series of tools that you can use to extract, transform, and merge data from different sources into single or multiple destinations. By using DTS tools, you can create customized data mobility solutions based on your company's specific needs, as in the following scenarios:

• You have deployed a database application on earlier versions of SQL Server or other platforms, such as Microsoft Access. The new version of the application now requires SQL Server 2000 and needs to change the database schema and transform some of the data types.

• To replicate and transform data, you can build a DTS solution that copies database objects from the original data source to a SQL Server 2000 database, and then reset the data bar and change the data type. You can use the DTS tool to run this solution, or embed the solution in your application.

• You must merge some of the key Microsoft Excel spreadsheets into your SQL Server database. Many departments create spreadsheets at the end of each month, but no schedule is set up to complete all spreadsheets.

• To incorporate spreadsheet data, you can build a DTS solution that executes when a message is sent to a message queue. This message triggers DTS to extract data from the spreadsheet, perform various defined transformations, and then load the data into the SQL Server database.

• Your Data warehouse holds historical data about business operations, and you use Microsoft SQL Server 2000 Analysis Services to summarize the data. This data warehouse needs to be updated every night from the online transaction processing (OLTP) database. While your OLTP system is in use 24 hours a day, its performance is critical.

You can build a DTS solution, use File Transfer Protocol (FTP) to move data files to a local drive, load data into the fact table, and then use the profiling service to count the data. You can schedule this DTS solution to be executed nightly at night, or you can use the new DTS logging options to track the time spent in this process, allowing you to analyze performance changes over time.

What is DTS?
DTS is a set of data transformation tools that you can use to import, export, and transform different types of data between one or more data sources, such as Microsoft SQL Server, Microsoft Excel, or Microsoft Access. The connectivity is provided through the open standard-ole db-of data access. The ODBC (Open database connection) data source is supported by OLE DB Provider for ODBC.

You can create a DTS solution as one or more packets. Each packet can contain a set of ordered tasks, define the work to be performed, or include transformations of data and objects, define workflow restrictions for task execution, and connect data sources and destinations. DTS packets also provide services such as recording packet execution details, controlling transactions, and handling global variables.

The following tools can be used to create and execute DTS packets:

• The Import/Export Wizard (Import/export Wizard) is used to build relatively simple DTS packets that support data migrations and simple transformations.

DTS Designer graphically implements the DTS object model, allowing you to create DTS packages with a large number of capabilities.

dtsrun is a command prompt utility that executes existing DTS packets.

Dtsrunui is a graphical interface for dtsrun, which also allows you to pass global variables and generate command lines.

SQLAgent is not a DTS application, but DTS can use it to schedule packet execution.

You can also use the DTS object model to create and run data packages programmatically, build custom tasks, and build custom transformations.

What's new in DTS?
Microsoft SQL Server 2000 introduces a number of DTS enhancements and new features:

• New DTS tasks include FTP tasks, performing packet tasks, dynamic property tasks, and Message queue tasks.

• Enhanced logging provides information about the execution of each packet, allows you to have a complete history of execution, and can view information about each step in the task. You can generate an exception file that contains rows of data that might not have been executed due to an error.

• You can save a DTS package as a Microsoft Visual Basic file.

• New multi-segment data pump allows advanced users to customize data conversion operations at different stages. You can also use global variables as input parameters for queries.

• You can use parameterized source queries in DTS transformation tasks and execute SQL tasks.

• You can use the Execute Packet task to dynamically assign the value of a global variable from a parent packet to a child packet.

Back to the top of the page
Using DTS Designer
DTS Designer graphically implements the DTS object model, allowing you to graphically create DTS packages. You can use DTS Designer to:

• Create a simple packet that contains one or more steps.

• Create packages that contain complex workflows that can contain connections that use conditional logic for multi-step operations, event-driven code, or multiple data sources.

• Edit existing packets.

The DTS Designer interface consists of work areas, toolbars, and menus. Where the work area is used to build the package, the toolbar contains the packet elements that you can drag onto the design page, which contains workflow and packet management commands.

Figure 1:dts Designer Interface
View the larger image.

In DTS Designer, you can easily create powerful DTS packages by dragging connections and tasks to the design page and specifying the order in which workflows are executed. The following sections define tasks, workflows, joins, and transformations, and describe how to easily implement DTS solutions using DTS Designer.

Tasks: Defining steps in a packet
A DTS packet typically contains one or more steps. Each task defines a work item that may be executed during the execution of a packet. You can use tasks to:

• Convert Data

Transform Data Task
Used to move data from source to target, you can choose to apply data bar conversions on the data.

Data-Driven Query tasks
Used to perform Transact-SQL-based flexible operations on data, including stored procedures and INSERT, UPDATE, or DELETE declarations.

?
Parallel Data pump tasks (1)
Can be used only for programming, parallel data pump tasks perform the same functions as transform data and Data driven query tasks, but support the "chapter-style" data rowset defined by OLE DB 2.5 and later versions.


• Copy and manage data

Bulk Insert Task
Used to quickly load bulk data into a SQL Server table or view.

Execute SQL Task
Used to run SQL statements during packet execution. The Execute SQL task can also save the query's result data.

Copy SQL Server Objects task
Used to copy a SQL Server object from one SQL Server installation or instance to another. You can copy objects such as data and tables, or you can copy object definitions such as views and stored procedures.

Transfer Database Tasks (1)
Use to move or copy a SQL Server database from an instance of SQL Server 7.0 or SQL Server 2000 to an instance of SQL Server 2000.

Transfer Error Messages Task (1)
Used to copy user-specified error messages from SQL Server 7.0 or SQL Server 2000 instances to instances of SQL Server 2000 that were generated by sp_addmessage system stored procedures.

Transfer Logins Task (1)
Use to copy an account number from SQL Server 7.0 or SQL Server 2000 instances to an instance of SQL Server 2000.

Transfer Jobs (1)
Use to copy jobs from SQL Server 7.0 or SQL Server 2000 instances to instances of SQL Server 2000.

Transfer Master Stored Procedures Task (1)
Use to copy a stored procedure from a master database in SQL Server 7.0 or SQL Server 2000 to the master database for instance of SQL Server 2000.


• Run a task as a job from a packet

ActiveX Script Task
Used to write code that performs functions that are not available in other DTS tasks.

Dynamic Properties Task (1)
Used to obtain numeric values from sources outside the DTS packet at run time and assign those values to the selected packet properties.

Execute Package Task (1)
Used to run other DTS packets from the packet.

Execute Process Task
Used to run an executable program or batch file.

FTP Tasks (1)
Used to download data files from a remote server or Internet location.

Message Queue Task (1)
Used to send and receive messages from Microsoft Message Queuing.

Send Mail Task
Used to send e-mail.

Analysis Services processing tasks (2)
Used to process objects defined in one or more SQL Server 2000 Analysis Services.

Data Mining Task (1,2)
Used to create a prediction query and output table from the data mining model object defined by the SQL Server 2000 profiling service.

New in 1 SQL Server 2000.

2 applies only to scenarios where the SQL Server 2000 Analysis Service is installed.

You can programmatically create custom tasks and then integrate them into DTS Designer using the Register custom Task command.

To illustrate the use of these tasks, here we have a simple DTS packet with two tasks: Microsoft ActiveX Script task and Send Mail task:

Figure 2: DTS packets with two tasks

ActiveX Script tasks can host any ActiveX scripting engine, including Microsoft Visual Basic scripting Edition (VBScript), Microsoft JScript, or Activesta Te ActivePerl (You can download from http://www.activestate.com/). The Send Mail task can send a message stating that the packet is already running. Please note that these tasks are not in order. When the packet is executed, the ActiveX Script task and the Send Mail task run concurrently.

Workflows: Setting Task priorities
When you define a set of tasks, it is common for tasks to be executed in a certain order. If these tasks have a certain order, then each task will be called a step in the process. In DTS Designer, you can manipulate tasks in the DTS Designer design page, using precedence limits to control the order in which tasks are performed.

Priority limits link the tasks in the packet to each other in turn. The following table shows the types of priority limits that you can use in DTS.

Priority Limit Description

On completion (after completion)
(Blue arrow)
If you want task 2 to be in a waiting state until task 1 completes (regardless of the outcome), use the On completion priority limit to link Task 1 to Task 2.

On Success (after successful)
(Green Arrow)
If you want task 2 to be in a waiting state until task 1 completes successfully, Link Task 1 to Task 2 using the on Success priority limit.

On failure (after failure)
(Red Arrow)
If you want task 2 to start only when task 1 fails to execute successfully, use the On Failure priority limit to link Task 1 to Task 2.

The following illustration shows an ActiveX Script task and a Send Mail task with the on completion precedence limit. When the ActiveX Script task completes (either successfully or unsuccessfully), the Send Mail task starts running.

Figure 3: ActiveX Script tasks and Send Mail tasks with on completion precedence restrictions

You can configure different Send Mail tasks, one for on Success limits, and another for on failure restrictions. These two send mail tasks can send different messages based on the success or failure of the ActiveX Script.

Figure 4: Mail tasks

You can also apply multiple priority limits on one task. For example, the Send Mail task "Admin Notification" can have an on Success restriction from the script # and an On failure restriction from scripting. In this case, DTS considers that it uses a logical "and" relationship. Therefore, in order to send the Admin Notification message, the script # must execute successfully, and the script must fail.

Figure 5: Example of multiple priority limits for the same task

Connections: Accessing and moving data

To successfully perform a DTS task that replicates and transforms data, the DTS package must establish a valid connection to its source and destination, as well as to other data sources (such as a query table)

When you create a packet, you can configure the connection by selecting the connection type from the list of valid OLE DB providers and ODBC drivers. The available connection types include:

Microsoft Data Access Component (MDAC) driver

Microsoft OLE DB Provider for SQL Server

Microsoft Data Link

Microsoft ODBC Driver for Oracle


Microsoft Jet Driver

DBase 5

Microsoft Access

HTML file (source)

Microsoft Excel 97-2000

Paradox 5.X


• Other drivers

Text file (source)

Text file (destination)

Other connections

DTS allows you to use any OLE DB connection. The icons in the Connection toolbar provide a convenient way to access common connections.

The following figure describes a packet with two connections. Data is copied from an Access database (source connection) to the SQL Server production database (destination connection).

Figure 6: Example of a two-connection packet
View the larger image.

The first step in this packet is an Execute SQL task that checks to see if the target table already exists. If it already exists, the table will be deleted and recreated. After successfully completing the Execute SQL task, the data is replicated in the second step to the SQL Server database. If the copy operation fails, an e-mail message is sent in step three.

Data pump: Converting data

DTS Data Pump is a DTS object that is used to drive data import, export, and transformation. This data pump will be used in conversion data, data-driven queries, and parallel data pump tasks. These tasks create a data row group in the source and destination connection, and then create a data pump instance to move the data rows between the source and the destination. Data transformations are performed on each row when the data row is replicated.

In step 2 of the following illustration, a Transform Data task is used between the Access DB task and the SQL Production DB task. The Transform Data task is a gray arrow between two connections.

Figure 7: Example of transforming data tasks
View the larger image.

To define the data that is collected from the source connection, you can create a query for this transformation task. DTS supports parameterized queries that allow you to define query values when the query executes.

You can type this query in the Properties dialog box for this task. Or you can use the Data Transformation Services Query Designer (Transformation Server query Designer) to create a query graphically for DTS tasks. The following illustration uses the Query Designer to build a query that adds three tables to the pubs database.

Figure 8: Data Transformation Services Query Designer interface
View the larger image.

In a transformation task, you can also define changes made to the data. The following table explains the built-in conversion capabilities provided by DTS.

Conversion description
Copy Data Bar
Used to directly copy data from the source to the destination data bar without any conversion to the data.

ActiveX scripts
Used to build custom transformations. Note that because conversions are done on a row-by-line basis, ActiveX scripts can affect the speed at which DTS packets are executed.

Date Event String
Use to convert a date or event in the source data bar to a different format in the destination data bar.

Lowercase letter String
The data type used to convert lowercase letters in the source data bar (if needed) to the destination data bar.

Uppercase string
The data type used to convert all uppercase letters in the source data bar (if required) to the destination data bar.

middle of string
Use to extract substrings from the source data bar, convert them, and then copy the results to the destination data bar.

Trim string
Use to delete the front, back, and middle whitespace in the source data bar and copy the results to the destination data bar.

Reading files
Use to open the contents of the file specified in the source data bar and copy its contents to the destination data bar.

Write to File
Used to copy the contents of the Source data column (data) to a file, where the path is specified by the second source data bar (file name).

You can also create your own custom transformations programmatically. The quickest way to create a custom transformation is to use the Active Template Library (active Template Library,atl) to customize the transformation template, which is included in the SQL Server, DTS sample program.

Data Pump error Log
SQL Server 2000 has a new way to record conversion errors. You can define three exception log files for packet execution procedures: Error text files, source error data row files, and destination error data row files.

• General error messages are written to the error text file.

• If the conversion process fails, the source data row will appear with an error and write the row to the source error data line file.

• If the insertion process fails, the target data row will receive an error and write the row to the destination error data line file.

The Exception log file is defined in the task that transforms the data. Each transformation task can have its own log file.

Data Pump phase

By default, the data pump has only one phase: data row conversion. This phase is the data bar transformation that you configure in the Transform Data task, the Data Driven Query task, and the parallel data Pump task without selecting the stage.

Most of the pump-phase functionality is added in SQL Server 2000. By selecting the multiple-segment data pump option in SQL Server Enterprise Manager, you can access the data pump and add functionality in different places during the operation.

When you copy a row of data from a source to a target, the data pump operates according to the basic program shown in the following illustration.

Figure 9: Data pump process
View the larger image.

After the data pump processing the last line of data, the task completes, the data pump operation is over.

If an advanced user needs to add functionality to the packet so that it supports any data pump phase, he can do this:

• Write an ActiveX script phase for each custom data pump phase. If you use the ActiveX scripting feature to customize the data pump phase, you do not need any code other than the packet.

• Create COM objects in Microsoft Visual C + + to customize the selected data pump phase. You develop this program outside of the packet, and each selected stage of the transformation invokes the program. Unlike the Actiex scripting method that accesses the data pump phase, the ActiveX scripting method uses different features and entry points for each selected phase, and this method provides a single entry point that is invoked by multiple data pump phases during task execution.

Back to the top of the page
Options for saving DTS packets
The following options allow you to save DTS packets:

Microsoft SQL Server

If you want to save the packets in an instance of SQL Server on any network, secure your DTS package on Microsoft SQL Server and keep a list of those packages, adding and removing the package versions during the packet development process.

SQL Server 2000 Meta Data Services

If you plan to track package versions, metadata, and data lineage information, keep the DTS data packets on the metadata service.

• Structured Storage files

If you need to copy, move, and send packets on your network, rather than storing the packets in a Microsoft SQL Server database, save the DTS package as a structured storage file.

Microsoft Visual Basic

If you want to integrate it into a Visual Basic program, or as a prototype developed by a DTS application, save the DTS packages created by the DTS Designer and DTS Import/Export wizards as Microsoft Visual basic files.

Back to the top of the page
Use DTS as an application development platform
DTS Designer provides solutions for a variety of data mobility tasks. DTS extends many of the available solutions by providing programmatic access to the DTS object model. Using Microsoft Visual Basic, Microsoft Visual C + +, or other COM-enabled application development systems, you can develop a custom DTS solution for your environment using features that are not supported in graphics tools.

DTS provides support for developers in a number of different ways:

• Building Data Packages

You can develop extremely complex packets that access all the features in the object model without the need to use the DTS Designer or DTS Import/Export Wizard.

• Extended Packets

You can add new features by customizing tasks and transformations that are tailored to your business and can be reused in DTS.

• Execute data packets

DTS packet execution does not need to be based on any provided tools to programmatically execute DTS packet and display procedures through COM events, allowing an embedded or custom DTS execution environment to be built.

The sample DTS program provided can help you understand DTS programming. These samples are installed with SQL Server 20,001.

If you are developing a DTS application, you can reassign the DTS files. For more information, refer to the Redist.txt file on your SQL Server 2000 CD.

More information
Microsoft SQL Server Books Online has a lot of information about DTS, using DTS applications, and building custom solutions. For additional information, refer to the following resources:

Microsoft SQL Server Web site, address http://www.microsoft.com/china/sql/.

Microsoft SQL Server Developer Center, address http://msdn.microsoft.com/library/default.asp?URL=/sqlserver/.

SQL Server Magazine, address http://www.sqlmag.com/.

Microsoft.public.sqlserver.server and Microsoft.public.sqlserver.datawarehouse newsgroups, address news://news.microsoft.com.

• Microsoft's official course on SQL Server (Microsoft Official Curriculum). For the latest course information, please visit the Microsoft Training and Services Web site at http://www.microsoft.com/traincert/default.asp

The information provided in this document represents only the temporary views of Microsoft Company on the issues surrounding the discussion activities on the day of the information release. Since Microsoft must constantly adjust to changing market conditions, the contents of this document should not be construed as any of the commitments made by Microsoft, and at the same time, Microsoft cannot continue to guarantee the accuracy of the information contained in the document at the date of release.

This white paper is intended for informational purposes only. Microsoft does not provide any form of warranty, express or implied in this document.

Compliance with all applicable copyright laws is the responsibility of the users of the document. Although Microsoft does not have any qualification under copyright for any rights relating to this document, no person shall, without the written authorization of Microsoft Company, be permitted for any purpose, in any form or by any means whatsoever (electronic, mechanical, photocopying, Recording, etc.) make any part of this document copy, store, or introduce a retrieval system, or transmit to any object.

Microsoft may have patents, patent applications, trademarks, copyrights, or other forms of intellectual property rights with respect to the subject matter covered by this document. Unless a written license agreement has been signed with a Microsoft company and expressly authorized under the terms of the agreement, any production of this document will not allow you to have permission to exploit the patent, trademark, copyright or other intellectual property.

? Microsoft Corporation. All rights reserved.

Microsoft, ActiveX, JScript, Visual Basic, and Visual C + + are registered trademarks or trademarks owned by Microsoft Companies in the United States and/or other countries.

The real names of the other companies and products involved in this document are trademarks held by their respective owners.