Error code: 0x80040E21 Reading from SSAS with SSIS

So, I upgraded a big project for a customer to 2012 from 2008R2. Everything worked fine, except the last part, where some datamarts was loaded with SSIS from SSAS. They had worked with no problem before, but suddenly, I received the above error code. It made no sense to me, and it was hard to find out, what precisely had happen. The worst, was, that I was able to see the preview in the datasource, but not run the package.

A workaround was to create a linked server to the cube, and then just select from that like so

SELECT * FROM openquery(<ServerName>, ‘<MDX>’)

And that was, properly they way I would have implemented it myself. But having hundreds of packages, rewriting them, was not the way to go.
Instead, I found an old post on google, where a person had the same problem, in an older SQL server. The solution was, to open the connection, click the all botton, and type in “(Format=Tabular)” in the extended properties field. Se the image bellow.

So, yeah…., YIEAKS. But hey, if you find yourself having the same problem, you now know what the solution is :-)

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>