The problem: I have a client who is running their application on SQL Server. They use SQL Server Analysis Services for additional reporting based on the cube that's been provided. It turns out, there is one query they are looking to run that is written in SQL and is fairly complex. The cube doesn't give them the data that they are looking for.
The workaround: As the client, they don't have access to directly query the database. Hence, they send me this query, and every week, I send them back the results on a spreadsheet.
Proposed solution: I believe I would be able to use SQL Server Integration Services to work around this whereby, I can automate the creation of a table with the results of said query which they would be able to query using their existing access to Analysis Services. Am I correct in saying this?
If so, I believe I need to create an SSIS package with certain control and data flows. What is the high level structure of what needs to be built?
P.S. In looking so far, I see a control flow task called "Execute SQL Task". I imagine I would have to build on this.