SSIS 2012 – Inserting data into a SQL Server Table from an MDX Query

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.

Advertisements

2 thoughts on “SSIS 2012 – Inserting data into a SQL Server Table from an MDX Query

  1. Reblogged this on The Daily DBA and commented:
    I recently had a need to extract the meta data for a tabular model into a database. Unfortunetly the AMO object model handles MOLAP and Tabular cubes very differently so I ended up using MDX queries against the $system.MDSCHEMA DMVs. I found the following blog post on using MDX queries in a SSIS package to be quite helpful when it came to integrating my MDX queries into my meta data population package.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s