The reason for this blog post, is I recently had a query if it was possible to get data returned from an MDX Query and then insert the data into a SQL Server Table.
And the reason for putting it into SSIS was that we could schedule the job to run on a schedule.
I have inserted pictures from my own documentation to save me some time publishing this post.
The reason that I also like using the ADO NET Source is that you can use Expressions for both your ADO NET Source and Destination.
For my requirement I needed to ensure that my MDX Query only got data for the previous week. So in order to do this I had to create Variables which would be populated in the previous steps in my SSIS Package.
NOTE: The Previous MDX query did not include getting the dates for the previous week.
I could then use the ADO NET Source.SqlCommand Expression.
And in this Expression I could then pass my variables to my MDX Query, to ensure that I could always get the previous dates data.
I then deployed my SSIS Project to the SSIS Server and scheduled the job as per the requirement.
And now it is running perfectly.