Through DTS services, enterprises can integrate multiple types of data sources to form a single multi-data integration industry.

Data Transmission Service (DTS) is used to integrate multiple types of resources in an enterprise to form a single multi-data integration business. Date: March 7, 2005

Author:Platform-based micro-indexing and Indexing

Local content

	Preface
	A tool for SQL Server to collect data
	Introduce basic functions of DTs
	Introduce ActiveX script definition Information
	Data transmission integrates front-end applications for data processing.
	Use DTS to build the concept of data structures
	Conclusion

Preface

The materials used by daily exchanges in enterprises may differ from those used by front-end systems, the most commonly used information is Microsoft SQL Server, Oracle database, IBM DB2, Sybase, Informix... And other options such as MySQL, progress, and access are not allowed. The materials produced by the Operation System in daily work all focus on the correctness of transaction information records and data processing, for example, the Changzhi sales system (POS) records the differences between the customer's products, the consumption amount and the payment method, focuses on the time for single-dataset entry and system response; Customer Relationship Management System (CRM) record the types of anti-issues, problem cases, and case progress of the customer, and manage the effectiveness of the handling process. When the supervisor wants to improve the customer's usage of production products, information Analysts must integrate the information of the customer's marketing system with the customer management system, find out the information required by the supervisor through the administrative program. In this way, human resources are processed without waste, making it easier to cause problems of data duplication and inconsistency.

The next response to resource processing is how enterprises can adapt to the increasingly changing business environment and cooperate with the marketing model, integrating a variety of traditional materials to meet users' needs is also the first step in business intelligence.

Return to the beginning

A tool for SQL Server to collect data

Bulk Copy Program ), you can insert a large number of new data columns into the SQL server data table or quickly extract data from the data table into the text format or native format. When using this utility, you must first understand the structure of a large number of metadata tables, and the effective information type of the Data columns in the data table. Generally, when the BCP application program is used for data migration, it can be divided into the following user hours.

1.	Storing data between servers If you want to allocate a large amount of data to another SQL Server database, the data of the source database must be carefully written to the case, then, a large amount of data will be transferred to the target database. BCP can use native, word, and Unicode formats to generate a large amount of additional data on different SQL Server instances of different processor architectures. However, the same format must be used for outbound and inbound requests. Non-character resources use native format to save time, avoiding unnecessary re-engineering between the resource type and character format. When you use a large amount of incoming data on servers that use different character pages, you can use the Unicode character format for all character information, this will avoid missing any extended characters (if the extended character is redirected to a non-Unicode row, it will not be able to represent the extended character, but it may still be missing ).
2.	Route the resources to the server Through BCP, the information in the data records is quickly transferred to the server, and the following rules are followed during the upload process: • When a large amount of data is written to a data table without indexes, we recommend that you set the original model of the data table to a large number of records. Use this option to prevent transaction reporters from consuming all hard disk space. • If the amount of data to be imported is greater than the amount of data in the existing data table, you can remove the indexes in the data table before performing a large number of operations. Conversely, if the data to be imported is relatively smaller than the existing data volume in the data table, removing the index is not necessary, because the time required for re-indexing may be longer than the time required for a large number of concurrent operations. • In parallel, multiple user-side resources are added to a single data table. This improves the efficiency of data integration. If you want to concurrently upload data to a large number of rows on SQL Server, the database usually uses a large number of records to record the original data and specify the tablock prompt.
3.	Convert the data from the metadata table into an example The results of the join query results of the explain statement can be first written into the explain statement table, and the results are output in the explain format. For example Bcp xdb. DBO. my_view out C:/my_view.txt-C-sservername-USA-ppassword
4.	Route the information of the query to the data source The BCP utility can merge the results of the SQL statement into the statement. The explain statement can be a valid statement that can be used to retrieve the result set. Select statements that can be used to query multiple data tables in a distributed manner. For example, if you want to migrate all the names from the employee data table of the northwind data source to EMP in the order specified by the ticket. in TXT information, run the following command: BCP "select firstname, lastname from northwind. DBO. Employees order by lastname" Queryout EMP. txt-C-sservername-USA-ppassword If the SQL statement returns multiple result sets, for example, the SELECT statement of the specified compute clause, or the statement that contains multiple SELECT statement statements, only the first result set will be created, and the result set will be omitted later.

BCP is located in C:/program files/Microsoft SQL Server/80/tools/binn/bcp.exe. BAT and. cmd. The usage is as follows:

Data Description

-F: use the graphic format to record the information related to the memory type, prefix length, response length, and Response Level of the response terminal.

-C: The result is processed in the text format, which is the most commonly used format. Other-N uses native) resource format processing involves non-extended/DBCS characters, Unicode characters use-W bytes Unicode data/non-extended/DBCS characters, Unicode native) use-N to handle extended/DBCS characters

-U: indicates the login token used. Generally, it must be the same as-P indicates the password used by the logon token.

-T

-H: Used in parallel with a large amount of incoming data

Generally, BCP is used to extract text data, and example is used.

C:/> bcp xdb. Owner. object out C:/object.txt-C-uxxx-pyyy-szzz

Use BCP labels to enter the text format, for example, the following example:

C:/> bcp xdb. Owner. Object in C:/object.txt-C-uxxx-pyyy-szzz

In addition, a large amount of metadata is written using the bulk insert statement, which can also import data from the data sheet to the data table. However, the bulk insert statement does not allow you to handle a large number of data records from an SQL Server user base to the data base. Instead, it serves as a processing industry. Bulk insert statements allow you to use the functionality of the BCP public program. The SQL statements allow you to upload a large amount of data to the resource, instead of the command prompt characters.

The data used must be stored on the server to eliminate the amount of data transferred from the application client to the server, the row permission limit is only valid for members of the system administrator (SysAdmin) and the bulkadmin server's fixed roles. Usage instructions are as follows:

Data Description

Batchsize: specifies the number of columns in a batch. Each batch is transferred to the server as a transaction. When an error occurs, the SQL Server recognizes or renews the original batch of transactions. For example, when a transaction is set to 2000, the transaction commit is performed for every 2000 bytes of information, if a loss or failure occurs during a transaction, the subsequent loss data will be overwritten. Such a setting can reduce the time when a loss occurs in the data source, the original metadata that requires a large amount of information.

Fieldterminator: Specifies the two-digit terminal to be used in the char and widechar data objects. The default value is/t (Tab character ).

Formatfile: Specifies the full path of the region, which can be the same as that of BCP.

Tablock. You can specify tablock to import the data table at the same time, greatly improving the efficiency.

Generally, bulk insert into is used to input the expected data table.

Raw text metadata used by BULK INSERT

Set bulk insert commands and response numbers

The result of creating rows after BULK INSERT

Return to the beginning

Introduce basic functions of DTs

Data processing services (DTS) provides a set of tools, allows programmers to retrieve, compile, and combine different sources of information, A variety of target data tables that can be supported by a single failover tool to address important business needs. You can create a data transmission tool for data transmission to block objects in tabular mode, or create a program to design the data transmission object model for data transmission, it is a tool for enterprises to customize their own resources. The main processing process of DTS is as follows:

The data processing service tools include the following:

•	DTS compaction inbound/outbound refined Compaction Through DTS internal import/export operations, you can write a prototype into and out of the prototype as a ticket. Then, you can use DTS design engineers to modify the prototype, you can use volume import/export to move schema/data from another location, perform the following operations: tables/views data migration, ad hoc data query, and tables, stored procedures, constraints, and indexes. In the migration process, you can add the script statement (VB script/JScript) from the notebook to compile the notebook statement of the notebook, and can be used with the scheduling settings of the SQL Server Agent to set the automatic operation, the prototype completed in the final step can be stored in SQL Server, meta data services, Visual Basic protocol, and structured protocol. You can use the following method to perform manual input/output operations: /program files/Microsoft SQL Server/80/tools/binn/dtswiz.exe manual or click "Submit inbound and outbound resources" in the dataset. When the dynamic button is used to import/export the dynamic plane after the refined button Select the expected data source, which can be the data source supported by oledb or ODBC. Select the expected data destination, which can be the data source supported by oledb or ODBC. Select the option to directly upload the data table and metadata table, use the lookup tool to send data, and upload the object and information. Select the expected data table or two-dimensional table Use the script statement to customize the special parameter settings of the data. Data transmission can be blocked, and the rows passed through the schedule can be written to the desired location. The final results can be directly merged and the rows of the final results can be seen.
•	Data Transmission Design The direct perception and modularity method is used to complete the data warehouse service. You can set up a single token and a token that contains the workflow of the Metadata Manager (for example, including branch, multi-step routing, multi-step routing, Zookeeper injection, and event Redirection Program blocking. The graphic user interface of data transmission design allows you to create and set the envelope by Using drag-and-drop to form a data transmission object metadata table. The user's interface includes: Depends on (connection) settings, can be based on different resources to set the source Work (task) is a complete work kit from basic information infrastructure to Operation Workflow allows you to specify whether or not the first line of work can be completed successfully, or when the first line of work fails, you can also use a condition to create a condition branch in the workflow, or a step without a condition will immediately begin the line, and several steps can be parallel.
•	Seal the administrative line utility The token can be opened through one of the two administrative line utilities, and the DTs run temporary utility and dtsrun command prompt the utility, the DTS run program public program can pass through the C:/program files/Microsoft SQL Server/80/tools/binn/dtsrunui.exe program line, after the operation, you can select the name of the domain name to which the operation is required. Specifies the hosts plane of the DTs run utility. You can select the names of the hosts that need to be written and directly upload the rows. In comparison, you can specify the variable value, the line record operator, and the generated dtsrun command prompt string.

Dtsrun utilities can publish messages created by data transmission. This token can be stored in MSDB data records, com-Based Structured memory instances, or meta data services. It is the encapsulation of the DOS command column, usage instructions are as follows:

Data Description

/F

/R: the consumer uses the metadata row meta data services to seal the consumer.

/S

/N: Specifies the name of the token to be merged.

/Z: Specifies to use the encrypted line string to upload rows.

In the following example, the name of the primary row is para_dts. The customerid is used in string (8) format and indicates that the partition is not imported to Windows event log, connect to the local server using the trusted connection method.

 

Return to the beginning

Introduce ActiveX script definition Information

In the data warehouse service, using ActiveX scripts to implement the data transmission object model's objects, features, methods, and collation function to charge the data transmission envelope function, activeX script Functions

•	You can format and modify the data from the origin site to the destination, and import the data using the conditional parameter to manage the workflow.
•	You can set up the number of data records processed by the data column, use and modify the value of global data changes of data transmission.
•	It can operate on the COM object created by the metadata access and utility functions.
•	Allows you to create and use ADO indexes, commands, resource sets, and other objects to access and operate data.

However, you must pay special attention to the use of ActiveX script statements to assist DTs in its operation, because the operation is completed only during the operation, thus reducing the efficiency, and the command will be executed once every time you attempt to block a bandwidth call, so the efficiency is the first level of information for your business, note the use of ActiveX script. The following example shows how to use ActiveX script to perform data lookup operations.

In addition to formatting and copying data from the origin site to the destination using ActiveX script, DTS still provides metadata types, including existing ActiveX scripts, copy column, date-time string, lowercase string, uppercase string, middle of string, trim string, and read file and Write File.

The following illustration shows that in addition to the use of visualized data transmission, data transmission can make the metadata more active, in general, the "one-to-one" operators, and the "many-to-many" and "non-homogeneous" operators are involved.

 

Return to the beginning

Data transmission integrates front-end applications for data processing.

In addition to the general routine operation and scheduling settings, the data processing service can still be integrated through front-end applications, optimize the application of zookeeper. First, data transmission and OpenRowset are merged. Through OpenRowSet, the caller can process the final result into a result set. In the process, the provider must use dtspackagedso, the usage can be select * or select * from <package name>. The following example uses the OpenRowSet function to merge the DTs data source with the SQL command for export.

In addition, DTS can combine with the linked server to query token messages. First, it must pass through the sp_addmediaserver method to transfer the token to the master. DBO. sysservers and use dtspackagedso as the resource provider. In the following example, the linked server is used to set the C:/Mers MERs. DTS sealing program to the Indexing Server, and then the SQL statements are used to directly query the data.

Second, the attacker line of the program can pass through the COM component call method of VBScript, directly apply the program line at the front end, and use DTs. the package object's loadfromsqlserver program is encapsulated in the upload program, and the program is blocked in the loadfromstoragefile upload row upload case. The following examples are examples of program blocking in the case of Using VBScript to upload rows.

 

Return to the beginning

Use DTS to build the concept of data structures

Data Warehouse can solve the sorting, adjustment, integration, and storage of large enterprise-level high-quality materials, A collection of unified data. It seems to be a super-large logistics center. The products (resources) come from different suppliers (transportation systems ), after moving, adjusting, and arranging (quenching and importing the plugin into management-etlm tool), traditional products are systematically placed on different racks) record and analyze the data volume (value) to facilitate customer selection) analyze the information and table production of commodities according to the favorite method and the way in which goods are imported ).

Data transmission design engineers can integrate multiple types of data into the cube of the Analysis Service, and then aggregate the data into the cube of the Analysis Service, calculate and save the required data values for each degree. In this way, the user's query speed can be accelerated. That is to say, it is convenient for the decision maker to check the region sales prices of specific products in a specific region in a specific year, in this way, the query uses three marketing editions-year, product, region, and end-of-year sales. In the cube, compute calculates all possible records so that all queries can receive the fastest response time. However, the general operation may require a large amount of memory space and processing time. The following cube allows the decision maker to perform data analysis based on the established degree. In the process, it must be completed with the design of the DTs seal.

 

 

Return to the beginning

Conclusion

The focus of the processing is that the tools that have passed the processing can save time and optimize the design, for example, BCP can be used to generate and inject a large number of Fixed-format resources, and bulk insert can be used as an example to import data. Using DTS, you can perform operations such as merge, merge, and configure operations, and use merge to import/export a precise sequence for the prototype of zookeeper, with DTS design, You Can slightly modify the internal volume of the zookeeper, and then use the SQL Server Agent with dtsrun for automatic scheduling. Integrating material sources through data processing tools will be the first project for enterprises to develop their business intelligence.