Data Transformation Services for SQL Server 2000 (bottom)

server| Data | Convert Data Transformation Services for Microsoft SQL Server 2000 (next)

Data pump: Converting data

A DTS Data pump is a DTS object that drives the import, export, and transformation operations of data.
Data pumps are used during data conversion, data-driven queries, and parallel data pump tasks. Practice
The process of doing these tasks is to create a rowset on the source and destination connections and then create the data pump solid
Example moves these rows between the source and the target. The conversion operation is performed when each row is replicated.

In the following illustration, in the second step, the Transform Data task is in the Access DB task and SQL
Between the Production db tasks. The Transform Data task is represented by a gray arrow between connections.

Figure 7. Example: Transforming Data tasks

To define the data collected from the source connection, you can generate a query for the transformation task. DTS Branch
Parameterized queries allow you to define query values when executing a query.

You can type the query into the Properties dialog box for the task, or you can use Data Transformation Services to check
The designer is consulted, a tool that graphically generates a DTS task query. In the figure below, the
Use the Query Designer to generate a query that joins three tables in the pubs database.
　　　　
Figure 8. Data Transformation Services Query Designer interface

In a transformation task, you can also define the changes you want to make to your data. The following table describes the DTS provided
The built-in conversions.



Conversion description
Replication columns are used to copy data directly from the source column to the target column without applying any transformations.
ActiveX scripts are used to generate custom transformations. Note that ActiveX scripts can affect the speed at which DTS software packages are executed, because conversions are done line by row.
A DateTime string is used to convert a date or time in a source column to another format in the destination column.
The lowercase string is used to convert the source column to lowercase characters and, if necessary, to the destination data type.
An uppercase string is used to convert a source column to a full uppercase character, and, if necessary, converts it to the destination data type.
The intermediate string is used to extract the substring from the source column and convert the result to the target column after it is converted.
The trim string is used to remove leading, trailing, and embedded spaces from the string in the source column, and then copy the results to the destination column.
The read file is used to open the contents of the file (the file name is specified in the source column), and then the content is copied to the destination column.
The write file is used to copy the contents of the source column (data column) into a single file whose path is specified by the second source column (the file name list).

You can also create your own custom transformations programmatically. The fastest generation of custom conversions
Using the Active Template Library (ATL) custom transformation template, SQL Server DTS
The template is included in the sample program.

Data Pump error logging

A new way to record conversion errors is provided in SQL Server 2000. You can define three
An exception record file to record the condition during package execution: error text file, source fault
False line file and target error line file.
Generic error messages are written to the error text file.
If the conversion fails, an error occurs on the source line, which writes to the source error line file.
If the insertion fails, the target row has an error and the row is written to the destination error line file.

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

Data Pump phase

By default, the data pump has a phase that is converted. This phase is in the no selection phase
In the case of transforming data tasks, data-driven query tasks, and parallel data pump tasks in the mapping
Configured at the time of the conversion of the column one level.

Multiple data pump phases are new in SQL Server 2000. Through the SQL Server
Enterprise Manager Select multi-stage data Pump option, in the data pump operation process of different stages are
You can access the data pump and add functionality.

When you copy data rows from the source to the target, the data pump will follow the basic process shown in the following figure
to operate.
　　　
Figure 9. Data pump Process

Data pump after processing the last line of data, the task is over, the data pump operation terminated.

If the advanced user wants to add functionality to the package so that the package can support any data pump order
section, you can do the following:
Write an ActiveX script phase function for each data pump phase that you want to customize. If
Use ActiveX scripting functions to customize the data pump phase without the need for this package
Any other code.
Using Microsoft Visual C + +? Create a COM object to customize the selected data pump order
Paragraph Develop this program outside of the package, during each selected stage of the conversion process
The program will be invoked. Each selected stage in the ActiveX scripting method that accesses the data pump phase
Use different functions and input points, and this method is different, it executes in the data Pump task
The period provides a single input point that is invoked by multiple data pump stages.

Options for saving DTS software packages

The following options can be used to save a DTS package:
Microsoft SQL Server
If you want to store the package on any instance of SQL Server on your network, this option will
The DTS package is saved to Microsoft SQL Server, leaving a handy soft
Package and to add and remove package versions from the package development process.

SQL Server 2000 Meta Data Services
If you are preparing to track package versions, meta data, and data series information, this option will DTS soft
The package is saved to the Meta data Service.

Structured Storage of files
If you want to copy, move, and send packages between networks without having to store them in Microsoft
SQL Server database, this option saves a DTS package to a structured storage file.

Microsoft Visual Basic
If you want to merge a DTS package into a Visual Basic program or use it as a DTS
A prototype developed with a program that will be created through DTS Designer or the DTS Import/Export Wizard
The built DTS package is saved to a Microsoft Visual Basic file.

DTS as an application development platform

DTS Designer provides a variety of solutions for data mobility tasks. Because provided by programming
Way to access the functionality of the DTS object Model, DTS expands the number of available solutions. Use
Microsoft Visual Basic, Microsoft Visual C + +, or any other COM-enabled
Application development systems, you can use features that are not supported by graphical tools to develop appropriate
A custom DTS solution for your environment.

DTS provides a variety of different ways for developers to support:
Build Packages
Without using DTS Designer or the DTS Import/Export Wizard, you can develop extremely complex
Package and access to the full set of features in the object model.

Extending packages
By building custom tasks and transformations, you can add something that works for your business and can be used in DTS
New features that are reused within.

Execute Package
It is not always necessary to use the tools provided to execute DTS packages, you can programmatically
Row DTS packages and displays progress through COM events and allows you to build embedded or custom DTS
Execution environment.

Sample DTS programs help you understand the basics of DTS programming. This sample can be used with SQL Server
20,001 set up.

If you are developing a DTS application, you can redistribute the DTS files. For more information, please
See Redist.txt on the SQL Server 2000 CD.

Excerpt from Http://www.microsoft.com/china/msdn/?MSCOMTB=ICP_MSDN