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.