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!!!
Thank you so much for posing this!!!