The Need for Transferring Data

If your company has a single database, in a single server, and you never need to receive data from other systems or send data to other servers, you could skip this chapter.

Many systems receive their data through direct user input. However, there are some cases where transferring data is important:

• You want to migrate to a new system and you want to populate the new database with data coming from your old system.

• Your accounting system works in a mainframe and you do not want to change this system. However, it would be useful to have some accounting information in the SQL Server Sales database. In this case, you must periodically refresh this information from the mainframe.

• The post office changes the national postal code information and they distribute this new information as a CSV file. You need to import this file into your system to update the Customer Management application.

• The Inland Revenue changes their requirements and now the annual accounts must be sent in a different format. You must create the process of exporting data in exactly the way they require.

• You create a testing server in your network and you want to have the same databases as in your production server to test a new indexing strategy.

• Your sales managers visit customers, and they want to have a copy of the Sales System database in their laptops so they can look at sales figures when they are at the customer site.

• Your corporation has many different companies in different countries, and you want to receive periodic financial information from them. Every one of these companies uses a different system, and the only way to receive data is by text files, so you can import them easily.

• You have a Documents database and you receive many documents from different sources. You want to import them into the Documents database efficiently.

• You are running a Geographical Information System and your field teams send you files every week with their field measurements. You need to integrate this new data with your existing GIS database.

• You just finished a new Agricultural Census in your county and you want to compare this new data with the latest census's data. The old data is in a different system and you want to import the old data to consolidate both databases.

• Your remote offices need to produce reports about their local sales figures. They complain because they need to access your central mainframe to produce these reports, but the mainframe connection is not always available. You decide that a good solution is to have a local database with local data to produce reports locally. You need to refresh these local databases periodically to have their data synchronized with the central database.

• Your network administrators are concerned about a potential bottleneck on your central database system. A feasible solution is to install departmental servers with replicated data. In this way, users can receive data from a local server, in the same network segment, without traversing the entire network to arrive to the data center.

SQL Server 2000 provides different tools to transfer data from any source to any destination. Depending on your specific requirements, one tool can be more appropriate than another. You will learn about the SQL Server 2000 tools used to transfer data in the next section of this chapter.

In other cases, the problem is not only transferring data, but also modifying data from the source database to meet the requirements of the destination database system. Some examples are as follows:

• You have a relational database and you need to create a data warehouse database with a different database schema; in this case, it could be a star schema.

• Your legacy system in the USA stores dates in a different format (mmddyyyy) from the legacy system you have in France (ddmmyyyy). You want to make sure you can import dates correctly to your central server in Indonesia, which uses the ISO/ODBC standard format (yyyy-mm-dd).

• After a company merge, you need to consolidate data from two different systems. In one system, the codes used in lookup tables are different from the codes used in the other system. In the Spanish system, end users can be S (solteros), C (casados), D (divorciados o separados), V (viudos). In the British system, end users can be S (single), M (married), D (divorced), W (widow or widower). You need to agree about new codes and transform the old ones.

• You just bought a bank in Morocco, and you see that their database system identifies customer accounts by their full name, including title. You want to provide a new account identification number and store title, family name, and first name in separate fields.

• You work in an international project and you need to integrate data in different currencies. Your system selects Euro as the standard internal currency and you must transform all quantities into Euros and store the exchange rate applied to every amount in a different field.

• You created a weather database to help on global weather forecasts. This system receives continuous information from weather systems around the world, each one using different units for temperature, rainfall, pressure, and so on. You must convert the data to uniform units to be able to produce consistent results.

Data Transformation Services 2000 can help you create complex packages that transfer and transform the data to meet the requirements of the destination database.