As far as I know, SSIS is going to be complicated to do this. You are basically going to need to build up a custom script in C# (or equivalent) to run in SSIS.
What is likely going to be the easiest way to do what you need (I am making a LOT of assumptions here) is have one workbook in Excel that connects to your SQL data source to pull in the raw data. Then use other workbooks to pull data from the Raw data via pivot tables for example.
Excel can either pull data directly from SQL or from another data source (such as SSRS). There are advantages and disadvantages to each approach, so pick one that works best for you.
Disadvantage to the "do it all in Excel" approach is end users need to click the "refresh all" button to make it refresh. You can have it auto-refresh on file load, but I find this sometimes causes issues so I do not recommend turning that on.