Data conversion Service (DTS)

Data conversion Service

 
DTS Basics
Many organizations need to integrate datasets to improve enterprise decision making. However, their data may be stored in different formats and distributed in different locations. Data conversion Service (DTS) provides a set of tools that allow you to extract, convert, and merge data from completely different sources to one or more purposes supported by DTs connectivity, to meet these important business needs. You can use DTS tools to generate data transmission packages graphically or use DTS object model to compile packages. This allows you to create custom data mobility solutions suitable for your organization's special business needs.

DTS package
The DTS package is an organized connection, DTS task, DTS conversion, and workflow constraint (the workflow constraint is assembled using DTS tools or programming and saved to Microsoft & reg; SQL Server 2000 and SQL Server 2000 meta data services are saved as a collection of Structured Storage files or Microsoft Visual Basic & reg; files.

When the package is running, each package contains one or more steps in sequence or in parallel. After the package is executed, it connects to the correct data source, copies data and database objects, converts data, and notifies other users or event processes. You can edit packages, protect passwords, schedule execution, and search by version.

For more information, see create a data transmission package.

DTS task
A Data Transmission task is a set of discrete functions that are executed as a single step in a package. Each task defines the work items to be executed as part of the data movement and data conversion processes, or as a job to be executed.

DTS provides many tasks that are part of the DTs object model and can be accessed graphically or programmatically through the DTs designer. These tasks can be configured separately for a wide range of tasks, including data replication, data conversion, and notification Status. For example:

Import and export data.
DTS can import data from text files or ole db data sources (for example, Microsoft Access 2000 database) to SQL Server. Alternatively, you can export data from SQL Server to OLE DB (for example, a Microsoft Excel 2000 workbook ). DTS also allows High-Speed loading of data from text files to SQL Server tables.

Convert data.
The DTS designer contains a data conversion task that allows you to select data from the data source connection, map data columns to a group of transformations, and send the converted data to the destination connection. The DTS designer also includes a data-driven query task that allows you to map data to parameterized queries.

Copy database objects.
In addition to data transmission, DTS can also transmit indexes, views, logon, stored procedures, triggers, rules, default values, constraints, and user-defined data types. In addition, scripts can be generated to copy database objects.

 

It indicates that there are some restrictions on this performance. For more information, see copy an SQL Server Object task.

Send messages to other users and packages, and receive messages from other users and packages.
DTS contains an email sending task that allows you to send an email when the package step is successful or fails. DTS also contains an execution Package task, which allows one package to run another as a package step and one message queue task, allowing you to use the message queue to send and receive messages between packages.

Execute a set of transact-SQL statements or Microsoft ActiveX & reg; scripts on the data source.
The execution of SQL and ActiveX script tasks allows you to write your own SQL statements and script code and execute them as a step in the package.

Because DTS is based on an extensible com model, you can create your own custom tasks. You can integrate custom tasks into the user interface of the DTs designer and save them as part of the DTs object model.

For more information, see DTS tasks.

DTS Conversion
DTS conversion is one or more functions or operations applied to a piece of data before the data reaches the target. The source data has not changed. For example, you can extract a substring from a column of the source data and copy it to the target table. This particular substring function is mapped to the conversion of the source column. You can also use some features (for example, a specific data value in a column) to search rows and apply functions only to the data in those rows. Conversion makes it easier to perform complex data verification, data cleaning, and transformation during the import and export processes. For column data, you can:

Manipulate column data.
For example, you can change the column type, size, decimal places, precision, or null.

An application is a function written as an ActiveX script.
These functions can be applied to specialized transformations or include conditional logic. For example, you can write a function in a scripting language to check data with a column value greater than 1000. If such a value is found, replace it with-1 in the target table. For rows with a column value below 1000, simply copy the value to the target table.

Select from several transformations provided with DTS.
For example, you can use a string or data format to reformat the input data, convert different strings, and copy the file content specified from the source column to the target column.

Write your own conversions to COM objects and apply those conversions to column data.
For more information, see DTS conversion.

DTS package Workflow
You can use the following method to define the sequence of steps in the package:

Priority constraints enable you to link two tasks based on whether the first task is executed, whether the task is executed successfully, or whether the task is not executed successfully. You can use the priority constraint to generate a condition branch in the workflow. Steps without constraints will be executed immediately and several steps can be executed in parallel.

Modify the ActiveX script of a workflow. For more information, see using ActiveX scripts in DTs.
For more information, see DTS package workflow.

Connectivity
Based on the ole db structure, DTS enables you to copy and convert data from different data sources. For example:

Use the local ole db provider directly from SQL Server and Oracle.

Use the Microsoft ole db provider of ODBC from the ODBC source.

Data sources from Access 2000, Excel 2000, Microsoft Visual FoxPro & reg;, dBase, paradox, HTML, and other files.

From a text file, use the built-in DTS flat file ole db Provider.

From Microsoft Exchange Server, Microsoft Active Directory, and other non-relational data sources.

Other data sources from third-party vendors.
DTS functions may be limited by the performance of specific databases, ODBC drivers, or ole db providers. For more information, see data conversion and conversion considerations.

For more information, see DTS connection.

DTS tools
DTS provides some tools to simplify package creation, execution, and management:

The DTS import/export wizard is used to generate packages to import, export, and convert data or copy database objects.

DTS designer, a graphical application that allows you to construct packages that contain complex workflows, multiple connections to heterogeneous data sources, and event-driven logic.

In the SQL Server Enterprise Manager Console tree, the "Data Conversion service" node is used to view, create, load, and execute data transmission packages, control data transmission designer settings, and manage execution logs.

Package execution utility:
The dtswiz utility starts the DTs import/export wizard by using the command prompt option.

The dtsrun utility prompts the running package from the command.

The DTS running utility (dtsrunui) allows you to use the dialog box running package.
The DTS query designer, a visual database tool, makes it easier to generate queries in the DTs designer.
For more information, see DTS tools.

Metadata
DTS provides the function of saving package metadata and data lineage information to meta data services and linking those types of information. You can store the directory metadata for the database referenced in the package and the accounting information for the historical records of specific data rows for the data mart or data warehouse.

For more information, see share metadata.

See

Prepare DTS applications

& Copy; 1988-2000 Microsoft Corporation. All rights reserved.