Firstly your initial approach if I'm interpreting you correctly is a very bad idea. What happens when a user needs to see more/different data on a report? Add new column, change the data extract for the "report" table? Going forward that's not really a viable development solution.

If you want to head down the data warehouse route then it would be useful to read widely around the subject. there are two main data warehouse methodologies out there - Kimball and Inmon. There are many books and online resources out there relating to both.

In terms of design and SSIS, yes you would need to design (and build) your data warehouse before embarking on developing an ETL. Without doing that you would have nothing to map to/load into.

Good luck.