Access to Legacy Applications and Data

  • Article

Previous TopicNext Topic

Import, Export, and Transform Database Data

Either interactively or automatically, DTS simplifies the process of importing and transforming data from multiple, heterogeneous sources. It supports data lineage, making it easy to track the origin of data. In addition, DTS enables you to move and transform data to and from the following sources:

  • OLE DB providers for SQL Server 7.0 and others

  • ODBC data sources such as Microsoft Access, Oracle, and DB2, which are using OLE DB provider for ODBC

DTS provides the functionality to import, export, and transform data between Microsoft SQL Server 7.0 and any OLE DB format. Using DTS, it is possible to build data warehouses and data marts in SQL Server 7.0 by importing and transforming data from multiple heterogeneous sources on a regularly scheduled basis (requiring no user intervention).

DTS imports and exports data between applications by reading and writing data in a common format. For example, DTS can import data from an ASCII text file or an Oracle database into SQL Server 7.0. Alternatively, data can be exported from SQL Server 7.0 to an ODBC data source, or a Microsoft Excel spreadsheet.

*Note * DTS only moves schema and data between heterogeneous data sources. Triggers, stored procedures, rules, defaults, constraints, and user-defined data types are not converted between heterogeneous data sources.

Transformations are applied to source data before it is stored in its new destination. For example, DTS allows new values from one or more source fields to be calculated. It also permits a single field that has been broken into multiple values to be stored in separate destination columns. Transformations make it easy to implement complex data validation, scrubbing, and enhancement during import and export.

DTS supports multistep packages, where multiple files can be processed separately, then brought together in a single, final step. Single records in a file can be broken up into multiple records in the destination, or multiple records in the source can be aggregated into single records in the destination.