How to transfer data from Analysis services using SSIS

Transfering data from SSAS to a destination table is bit tricky.

Using SSIS you can create a source connection property using OLEBD for Analysis Services source connection. But the problem with this provider is that although you would be able to parse and preview the results of the MDX query on the SQL Command window, it does not execute during run time.

There are 2 workaround for this. Use OpenQuery with Linked server to execute the MDX under a SQL Server Native provider (which has overheads and an effective solution). Or use ADO.net provider to execute MDX query by writing it to Datareader.

The below link explains the steps to transfer data using ADO.net provider.
http://bhavikmerchant.wordpress.com/2009/09/06/using-mdx-queries-in-ssis-without-openquery/

I found that the above solution only works with SQL 2005 where it has a Datareader Source data flow task.

Another workaround I found is to use the Execute SQL task to get the MDX result set and loop through the result set to insert data into destination table. Below is the screen shot of sample Control flow task with annotation.