First, I ask that you not ask 'why.' In the famous words of Tennyson "Ours is not to reason why. Ours is but to do and die." It's one of those, "This is what you have, deal with it." situations.
The source data comes from SSRS report. The goal is to load the data into a database via SSIS. The hopeful goal is to avoid human intervention in having to download the SSRS report into Excel or CSV.
There will be complex SSIS processing from there on out.
Any suggestions are humbly appreciated.
I would use the ReportServer Web service (this is used by the ReportManager UI)
This can be access via URL Access where you can specify a render format (CSV etc).
I'm not sure if the Web Service SSIS task will handle the return file for you though: I haven't tried it.
Using the web service is best. If the service isn't an option there are a few other alternatives. First, SSRS can save the report to the file system which you can then pick up with SSIS. Second, SSRS can email the report. You would then have to write or include an email fetch task in SSIS to grab the message and parse it or the attachment. Third, SSRS has decent API's delivery methods and even file formats. If none of the above options work you could roll your own delivery and file format.