Expert SQL Server™ 2005 Integration Services

1.1. Choosing the Right Tool for the Job

If you have any inclination toward home remodeling, chances are you enjoy walking through the tools area of your local home improvement store. Hundreds of different tools have been manufactured that perform a variety of functions and, in some cases, some fairly esoteric uses.

Any novice handyman can attest to the adage that the right tool for the job makes the job easier. The same concept applies when it comes to handling data. There's no doubt that, depending on the right situation, there may be a specific tool to handle such a function. Think about all the different types of data processing needs that you have across your organization:

• Data synchronization between systems

• Data extraction from ERP systems

• Ad hoc reporting

• Replication (both homogeneous and heterogeneous)

• PDA data synchronization

• Legacy system integration

• Vendors and partner data files integration

• Line of business data

• Customer and employee directory synchronization

• Data warehouse ETL processing

As you may know, when it comes to data processing, there are a lot of tools out there. Some are created for specific situations (such as folder synchronizing tools), whereas other tools are designed to perform a variety of functions for different situations. So, the traditional question often posed is which tool can best meet the business and logical requirements to perform the tasks needed?

Consider the host of tools found in the ever-evolving Microsoft toolset. You can use Transact SQL ...