SSIS: Extract data from the cube (via MDX) fails, OLEDB extended properties

This is a short post just to highlight a small setting which is critical to the task at hand !!!

I was trying to import cube data (MDX query result set), to my SQL table.

So I set up my MDX as usual with an OLEDB connection to the cube (source), and similar one for the destination (SQL server) within the SSIS project.

Then I dragged a data flow task and ended up with the set up as below:

Solution Structure:

Data flow Task:

DFT components:

During the source configuration, my preview works perfectly fine and shows me the intended result of the MDX query which i would like to import.

But when I run the package, I am struck with an Error as follows:

And in the details, I get the following:

Ooooh… thats not so great excetion detail to work with !!!

[OLE DB Source [8]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E05.

But well, no worries I have the solution to this problem. And that’s the core of this post…

Solution:

Open your OLEDB cube connection, and click on its All properties tab, and navigate to the “Extended Properties” and dump the following text in the same:

“Format=Tabular”

And thats it…

Voila, now you run your same package and there you go, it works exactly as you wished for it…

Hope it helps!!!