SQL Server™ 2005 Bible

Chapter 42. ETL with Integration Services

In This Chapter

• Integration Services variables and expressions

• Constructing control and data flows

• Package event handlers

• Debugging

• Full list of package elements

• Configuring packages for multiple environments

• Package deployment

Integration Services is most commonly described as an extract-transform-load (ETL) tool. ETL tools are traditionally associated with preparing data for warehousing, analysis, and reporting, but Integration Services represents a step beyond the traditional role. It is really a robust programming environment that happens to be good at data and database-related tasks.

Many prospective Data Transformation Services (DTS) users have been intimidated by the learning curve, sticking to the Transact-SQL they know instead of investigating a more powerful ETL tool. This has made traditional SQL approaches one of the largest competitors for DTS/Integration Services, but those who investigate Integration Services will find several advantages:

• Simple, fast methods for moving large quantities of data, minimizing database load and batching data into destination tables to keep blocking and transaction log sizes down.

• The capability to chain together many tasks, with complete control over ordering, and error and exception handling. Many tasks can be executed in parallel.

• Connections to read or write most any type of data without special programming or linked server calls.

• Common data and database management tasks are implemented without the need ...