SQL Server 2000 data conversion service (II)

Data Pump: conversion data

DTS Data Pump is a data transmission object that drives data import, export, and conversion operations.
Data pumps are used during data conversion, data-driven queries, and parallel data pump tasks. Executive
The process of running these tasks is to create a CCB set on the source and target connections, and then create a data pump instance.
For example, move the rows between the source and target. When copying each row, the conversion operation is performed.

In the following figure, in the second step
Production DB tasks. The conversion data task is indicated by the gray arrow between connections.

Figure 7. Example: data conversion task

To define the data collected from the source connection, you can generate a query for the conversion task. DTS support
With parameterized query, the query value can be defined during query execution.

You can enter the "properties" dialog box for the query task, or use the data conversion service to query
The query designer is a graphical tool used to generate a data transmission task query. In the following figure
Use the Query Designer to generate a query that is connected to the three tables in the pubs database.

Figure 8. Data Conversion Service Query Designer interface

In a conversion task, you can also define the changes to the data. The following table describes
Built-in conversion.



Conversion description
The copy column is used to copy data directly from the source column to the target column without any conversion.
ActiveX scripts are used to generate custom conversions. Note that the conversion will be performed row by row, and the ActiveX script may affect the running speed of the DTS software package.
DateTime string is used to convert the date or time in the source column to another format in the target column.
A lowercase string is used to convert the source column to a lowercase character. If necessary, it is converted to the target data type.
An uppercase string is used to convert a source column to a fully uppercase string. If necessary, it is converted to the target data type.
The intermediate string is used to extract a substring from the source column. After conversion, the result is copied to the target column.
Trim the string to delete leading spaces, trailing spaces, and embedded spaces of the string in the source column, and then copy the result to the target column.
Read the file to open the file content (the file name is specified in the source column), and then copy the content to the target column.
The Write file is used to copy the content of the source column (data column) to a file. The path of the file is specified by the second source column (file name column.

You can also use programming to create your own custom conversions. Generate the fastest custom conversion
The method is to use the active Template Library (ATL) to customize the conversion template, SQL Server 2000 DTS
This template is included in the sample program.

Data Pump error records

SQL Server 2000 provides a new method for recording conversion errors. Three definitions
Recording files to record the situation during the execution of the software package: error text file, source error
Line object and target line object.
● The error message is usually written into the error text file.
● If the conversion fails and the source row encounters an error, the row will be written to the source error row file.
● If the insert operation fails and the target row has an error, the row will be written to the target row file.

The exception log file is defined in the task for data conversion. Each conversion task has its own
Your log files.

Data Pump stage

By default, the data pump has a phase, that is, row conversion. This stage is in the unselected stage
In the case of data conversion tasks, data-driven query tasks, and parallel data pump tasks
Column-level conversion is configured.

Multiple Data Pump phases are added in SQL Server 2000. In SQL Server
Select the multi-phase data pump option in the enterprise manager.
You can access the data pump and add features.

When you copy data rows from the source to the target, the data pump will follow the basic processes shown in the following figure.
.

Figure 9. Data Pump process

After the data pump processes the last row of data, the task ends and the data pump operation ends.

If advanced users want to add functionality to the software package so that the software package can support any data pump level
You can perform the following operations:
● Compile an ActiveX script stage function for each data pump stage to be customized. If
Use the ActiveX script function to customize the data pump stage.
Any other code.
● Use Microsoft Visual C ++? Create a COM object to customize the selected data pump level
. This program is developed outside the software package and is executed at each stage selected during the conversion process.
The program will be called. Each stage selected in the ActiveX script method for accessing the data pump stage
Different functions and input points are used, and this method is different, it is executed in the data pump task
A single input point is provided during the period, which is called by multiple data pump stages.

Options for saving the DTS package

The following options can be used to save the DTS software package:
● Microsoft SQL Server
If you want to store the software package to any instance of SQL Server on the network, this option will
The data transmission software package is saved to Microsoft SQL Server, keeping a handy software
Package List, and add and delete the package version in the software development process.

● SQL Server 2000 metadata service
If you want to track the software package version, metadata, and data series information
Package is saved to the metadata service.

● Structured storage file
If you want to copy, move, and send software packages between networks without storing them in Microsoft
In the SQL Server database, this option saves the DTS package to a structured storage file.

● Microsoft Visual Basic
If you want to merge DTS software packages into Visual Basic programs or use them as DTS applications
A prototype developed by a program. This option is created by the DTS designer or the DTS import/export wizard.
The data transmission software package is saved to the Microsoft Visual Basic file.

DTS as an application development platform

The DTS designer provides a variety of solutions for data movement tasks. Because programming
Data transmission extends the number of available solutions. Use
Microsoft Visual Basic, Microsoft Visual C ++, or any other
Application development systems can use functions not supported by graphical tools to develop suitable
Custom DTS solution for your environment.

DTS provides developers with support in different ways:
● Generate a software package
Without the need to use the DTS designer or DTS import/export wizard, you can develop extremely complex
And can access the full set of functions in the object model.

● Extended software package
By building custom tasks and transformations, you can add services that are applicable to you and
New features used repeatedly in.

● Execution package
It is not necessary to use the provided tools to execute the DTS software package.
The data transmission package in line displays the progress through COM events, and allows the construction of embedded or custom data transmission.
Execution environment.

The sample DTS program helps you get started with DTS programming. This sample can be used with SQL Server
2000 install together.

If you develop a data transmission application, you can resend the data transmission file. For more information, see
See Redist.txt on the SQL Server 2000 CD.