Shulou(Shulou.com)05/31 Report--

This article is about the usefulness of SQLServer data conversion services. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

Database administrators may need to use all kinds of databases when dealing with databases, so how to analyze the data of different formats and different geographical locations together? in order to solve this problem, a service called DTS (data conversion) is proposed in SQLServer database. Through this tool, database administrators can extract, transform and even merge data from different sources (in different formats) to a specific purpose (such as SQLServer database) to meet the needs of statistical analysis.

First, import and export data.

When enterprises deploy information projects, one of the headaches is the problem of basic data import. Now most enterprises must have a certain level of informatization when implementing information projects, such as ERP projects. In the simplest sense, at least office software such as Excle may have been used to record some information, such as product information or basic customer information. Then, when they deploy the ERP system, if the employees enter the information one by one, it will add a lot of extra work. As far as I know, many enterprises make a little modification on the basis of the original data, such as removing some unused content or modifying some inaccurate information, and then directly import it into the database.

However, in the process of import, the format is not compatible or other problems often lead to the failure of the import. Using common tools, such as the import tool that comes with SQLServer, these error messages cannot be logged. Using these tools to import data, if you encounter an error, the import process will be terminated. In this way, the database administrator needs to re-check the data source (sometimes the database does not prompt which record has a problem). It is very inefficient to import data in this way.

The data conversion service can help us solve this problem. For example, data conversion services can import data in text format or from Excel files into SQLServer databases. Most importantly, if there is a problem with a record in the original data source, the entire import process will not be interrupted. After the import is complete, the data conversion service generates a report. In this report, the records that were not successfully imported and the problems that may be encountered will be recorded in detail. If the record is few, the database administrator only needs to enter it manually; and if there is more data, the database administrator can also import data in batches and import records that meet the requirements; and then modify those records that do not conform to the format according to the import report. After the modification is completed, continue to import the remaining data.

Obviously, it is much more convenient to import records in different formats through this data conversion tool. At the same time, the data transformation service can also export the contents of the SQLServer database to specific objects, such as Excel tables.

Therefore, the import and export data function of the data conversion service is of great use to guide between the import and export of some large amounts of data and data sources of different formats. For example, the data conversion service can import large-capacity records in text file format into SQLServer database at a high speed, and so on.

Second, use the data conversion specification to import the format of the data.

The data conversion service allows the database administrator to make some format adjustments or use some functions to adjust the format of the data to be imported without making changes to the original data into the SQLServer database. For example, now the database administrator needs to import data from an Excle table, but there is a column of item number in the Excle table, which is character data. However, in SQLServer databases, integer data is required. Without the help of a data conversion tool, the database administrator needs to convert the format in the Excel table before importing the converted data into the SQLServer database. If you use the data conversion service to import data, then in the import process, you can use the function to convert the data type without modifying the original data source.

The author talks about a real case that I have encountered. Once the author encountered this situation when he was importing basic information to a customer. Before they used the SQLServer database, they used the ACCESS database. There is a basic product information table in this database, including product keywords, product classification, and so on. When you need to import the contents of this database into SQLServer, add different prefixes to the product number according to the product classification. If the product is a finished product, add P before the product number; if the product is a packaging material, add B before the original product number; if the product category is parts, add M, and so on.

At this point, the author did not make any changes to the original data source. Instead, the DTS service is used to adjust the format by using related functions, such as character data merging, before importing the data into the SQLServer database. Therefore, one of the benefits of data transformation services is that you can standardize the data format that needs to be imported without changing the original data source. This is very useful in importing heterogeneous data sources into each other.

Another colleague of the author has encountered similar problems. For example, when he imports the inventory table to the user, he also needs to make some data conversion. When the inventory quantity is greater than or equal to 0, the imported value is the original inventory quantity. If the original database inventory quantity is less than 0, the imported inventory quantity is 0. In the process of import, my colleagues simply wrote an ActiveX script to achieve this requirement. In this script, functions such as IF can be used for data conversion, because these functions can be applied to specialized transformations or contain conditional logic. Thus, the record can be converted into appropriate data or format according to different conditional logic.

Therefore, in the process of importing data, if you want to convert some data in format or other aspects, the author suggests that we use DTS to convert the data and import it into the database.

Third, copy the database objects during the import process.

If you directly use ODBC and other tools to import the data from other SQLServer databases into the SQLServer database, you can only import the data, but not copy the objects on the database tables, such as constraints, indexes, primary keys and so on. Also, database objects based on underlying tables such as views, stored procedures, default values, and so on, cannot be imported.

In SQLServer databases, database administrators can copy objects from one instance of the database to another through the database object replication task. You can transfer basic objects or copy the definitions of some objects. If an object is selected, the copy object task will automatically copy the functions of any associated objects, such as table lakes or views. The premise is that these copied objects have foreign key constraints on the selected objects.

However, while importing data with DTS services, you can also copy database objects and transfer data types such as views, stored procedures, triggers, rules, default values, and user-defined data types. This is a function that cannot be achieved by data import tools such as ODBC. If you want to copy a large number of database objects, the database administrator can also copy these database objects through scripts.

Replicating database objects plays an important role in practical work, which can ensure the consistency and integrity of data import. For example, now the database administrator wants to import customer information tables from other databases. The customer information table needs to reference two tables: the contact information table and the address information table. That is, there are two fields in the customer information table that are foreign keys for these two tables. Then through the replication object job, as long as the customer information table is copied, the database will automatically replicate the tables or other database objects associated with it. Instead of having to import other related data manually by the database administrator.

Fourth, perform some automated operations.

In database management, the database administrator wants the database to collect information automatically and send it to the location specified by the user, such as mail, and so on. In the data conversion task, a series of similar tools are provided to simplify the work of database administrators. For example, in the DTS group, there is a tool for sending mail. With the relevant settings, the database will automatically send an email to the database administrator when the package succeeds or fails to remind him whether the job is successful or failed. And more detailed information will be attached to facilitate the database administrator for follow-up operations.

The data conversion function also includes an execute package task, one of which runs the other as a package step. This can make our work more automated. For example, in the process of database import, we can write the data tables that need to be imported step by step into the execution package plan through the package run plan. And let the database automatically import data in order when it is relatively idle. And the database administrator does not have to command around.

It can be seen that the data conversion service is a highly functional service. In the process of data import and transformation, the author first introduces this tool. Especially in the case of a large amount of data, this data conversion service can really play the role of the finishing touch.

Thank you for reading! This is the end of the article on "what is the use of SQLServer data conversion service". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.