we host clients using our own app so we also have multiple servers, multiple databases, all identical.

We built a source component that "makes the rounds" to run the same query against each database and inject the records to the data flow, from which I can do whatever transforms, cleansing, lookups, etc., en masse. each pkg I make usually populates just one DW table, sometimes more. We also have hundreds of tables in the source dbs, growing all the time.

Agree that duplicating all your live data is unnecessary, and costly on many fronts. Take what you need when you need it. I would otherwise have to constantly modify the 'go get everything' because we are continually growing our core product, so tables are added, decommissioned, fields added/modified/removed...