1、英文摘要 Data Transformation Services DTS facilitates the import, export, and transformation of heterogeneous data. It supports transformations between source and target data using an OLE DB-based architecture. This allows you to move and transform data between the following data sources: Native OLE DB
2、providers such as SQL Server, Microsoft Excel, Microsoft Works, Microsoft Access, and Oracle. ODBC data sources such as Sybase and Informix using the OLE DB Provider for ODBC. ASCII fixed-field length text files and ASCII delimited text files. For example, consider a training company with four regio
3、nal offices, each responsible for a predefined geographical region. The company is using a central SQL Server to store sales data. At the beginning of each quarter, each regional manager populates an Excel spreadsheet with sales targets for each salesperson. These spreadsheets are imported to the ce
4、ntral database using the DTS Import Wizard. At the end of each quarter, the DTS Export Wizard is used to create a regional spreadsheet that contains target versus actual sales figures for each region. DTS also can move data from a variety of data sources into data marts or data warehouses. Currently
5、, data warehouse products are high-end, complex add-ons. As companies move toward more data warehousing and decision processing systems, the low cost and ease of configuration of SQL Server 7.0 will make it an attractive choice. For many, the fact that much of the legacy data to be analyzed may be h
6、oused in an Oracle system will focus their attention on finding the most cost-effective way to get at that data. With DTS, moving and massaging the data from Oracle to SQL Server is less complex and can be completely automated. DTS introduces the concept of a package, which is a series of tasks that
7、 are performed as a part of a transformation. DTS has its own in-process component object model (COM) server engine that can be used independent of SQL Server and that supports scripting for each column using Visual Basic and JScript development software. Each transformation can include data quality
8、 checks and validation, aggregation, and duplicate elimination. You can also combine multiple columns into a single column, or build multiple rows from a single input. Using the DTS Wizard, you can: Specify any custom settings used by the OLD DB provider to connect to the data source or destination.
9、 Copy an entire table, or the results of an SQL query, such as those involving joins of multiple tables or distributed queries. DTS also can copy schema and data between relational databases. However, DTS does not copy indexes, stored procedures, or referential integrity constraints. Build a query u
10、sing the DTS Query Builder Wizard. This allows users inexperienced with the SQL language to build queries interactively. Change the name, data type, size, precision, scale, and nullability of a column when copying from the source to the destination, where a valid data-type conversion applies. Specif
11、y transformation rules that govern how data is copied between columns of different data types, sizes, precisions, scales, and nullabilities. Execute an ActiveX script (Visual Basic or JScript) that can modify (transform) the data when copied from the source to the destination. Or you can perform any
12、 operation supported by Visual Basic or JScript development software. Save the DTS package to the SQL Server MSDB database, Microsoft Repository, or a COM-structured storage file. Schedule the DTS package for later execution. Once the package is executed, DTS checks to see if the destination table a
13、lready exists, then gives you the option of dropping and recreating the destination table. If the DTS Wizard does not properly create the destination table, verify that the column mappings are correct, select a different data type mapping, or create the table manually and then copy the data. Each da
14、tabase defines its own data types and column and object naming conventions. DTS attempts to define the best possible data-type matches between a source and a destination. However, you can override DTS mappings and specify a different destination data-type, size, precision, and scale properties in th
15、e Transform dialog box. Each source and destination may have binary large object (BLOB) limitations. For example, if the destination is ODBC, then a destination table can contain only one BLOB column and it must have a unique index before data can be imported. For more information, see the OLE DB fo
16、r ODBC driver documentation. Note DTS functionality may be limited by the capabilities of specific database management system (DBMS) or OLE DB drivers. DTS uses the source objects name as a default. However, you can also add double quote marks (“ “) or square brackets ( )around multiword table and c
17、olumn names if this is supported by your DBMS. Data Warehousing and OLAP DTS can function independent of SQL Server and can be used as a stand-alone tool to transfer data from Oracle to any other ODBC- or OLE DB-compliant database. Accordingly, DTS can extract data from operational databases for inclusion in a data warehouse or data mart for query and analysis.
