Extracting data from cube using SSIS

I had a requirement of extracting data from SSAS cube and loading the same into some tables.
We have two options to do the same:
1. Extracting data directly from cube using MDX queries.
2. Extracting data from using using linked server.

In option 1, we directly query the cube writing MDX as per the requirement. To get the MDX I used proclarity where we just need to drag and drop the items needed accordingly and MDX is created in the background. Copy the MDX and optimize the same if needed.
This approach is neat and clean and have less maintenance cost and effort.

In option 2, since we are using linked server, we need to extract the data using OPENROWSET function.
Syntax:
OPENROWSET ( 'provider_name' , { 'datasource' ; 'user_id' ; 'password' 'provider_string' } , { [ catalog. ] [ schema. ] object 'query' } )
e.g. SELECT a.* FROM OPENROWSET('MSOLAP','seattle1';'manager';'MyPass', 'MDX query') AS a
Since Linked server is an extra thing to maintain here, the cost is high and not clean.
One more difference between the two is Options 1 is executed directly on cube, whereas Option 2 is executed on the datamart on which cube is based.

Now let us go through the steps in SSIS:
1. OLEDB as a source and use MDX expression as a SQL command. The output here in some clumsy form, so we will first clean the same. This will be done in the next next.
2. Add Copy column. This is just to provide the columns a simple name that can be used in the derived columns or any other transformation later.
3. Derived column to change the data types accordingly which matches with the destination tables.
4. OLEDB destination. make proper mappings here and the package is ready.
You can add any needed transformations in the flow in between.
Note: To execute MDX expressions to extract the data, MSOLAP provided is a must to be present in the server.

For more details or if you face issues with this, contact me on mrvijaykr@gmail.com ... Happy coding.