We do it somewhat different to Mark, our staging table are exact replicas of the source data, this allows us to use Bulk Copy when loading the data. It also insures there are no errors in the Load part of the ELT process.
We then use stored procedures to do the transforms to the production format (these may also be staging tables in some solutions). The major benefit is that you are doing any transforms on a limited data set and it is easy to debug the procedure. It then becomes a simple matter to dump in the staging data to the production tables.
I find a well crafted ELT process leave any ETL application in the dust. There are some ETL apps that this may not apply to but the cost is extraordinary.
Never underestimate the power of human stupidity