One of the new features within SQL Server 2008 is the Change Data Capture (CDC). CDC is designed to capture insert, update and delete activity on a SQL table and place the information into a separate relational table. It uses an asynchronous capture mechanism that reads the transaction logs and populates the CDC table with the row’s data which change. The CDC table mirrors the column structure of the tracked table, together with metadata regarding the change.
This is an ideal feature fot ETL now you can get all the data changes for a table without adding code or making triggers.
For more info see: Channel 9 webcast and The databasejournal