Hi Jamie, thanks for the article. I am yet to try out 2005, but am particularly interested in SSIS. I don't have a broad range of ETL experience, but the ETL work I do largely consists of DTS packages that call Stored Procedures. In essence, the DTS is only really there to help control workflow.
In your article, you discuss the 'E' and the 'L', but what about the 'T'? From what you have seen of SSIS, how well (or otherwise) does it handle transformations? Will we still be using Stored Procedures to do the 'real' work - such as deciding when to update a dimension record instead of inserting a new one. In DTS, we can use lookups in a Data Driven Query Task to achieve this, but the row-by-row processing has put us off due to the performance overhead incurred when dealing with very large dimension/fact loads. Hence the reliance on Stored Procedures and the set-based processing that allows.
I would LOVE to be able to perform the complete end-to-end ETL processing in a standardised environment (like I am told a 'real' ETL tool does - no I haven't used any others!), and am really hoping that SSIS is the answer to this.