Exporting MDX Query Data with SSIS

Query from the data flow without having to use a linked server or a script task

By Dan Meyers

I was recently working on a Scorecard application that displays KPI information from multiple data sources which included a few Analysis Services cubes. This required me to extract some KPI values from the cubes and place them into a SQL Server table (they did not have PerformancePoint up and going yet). I wanted to do this using a data flow in a SSIS package but ran into an issue that I thought had been fixed.

I was attempting to export the results of my MDX queries to a SQL Server table using a normal data flow task when I encountered one of Microsoft’s very un-helpful error messages. The OLE DB source adapter that runs the MDX query against the cube was failing and reporting the error message below.

SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E21.

After doing some searching, I was surprised to find out how many people are having issues trying to accomplish this. Even more surprising was the fact that Microsoft has not been very proactive in providing developers with a more direct method of doing this that does not produce errors out-of-the-box. This drove me crazy for hours before I figured it out. The annoying part of it was that in the UI in SSIS you can successfully preview the results on your query and even get the column definitions back but it would not run when you executed the package or data flow task.

Now, there are a couple of alternative approaches that you could use here. The two most popular ones that I have seen mentioned on the web are: to use a SQL Server Linked Server and a SQL Server data source to pass through your MDX query to the SSAS server and return a tabular dataset; or to substitute the OLE DB data source adapter with a Script Component and ADOMD.NET to create a dataset from the results returned.

The linked server approach seems to work just fine but is still a “work around” and not advisable. The Script Component requires a bit of scripting and is more cumbersome to implement.

Instead of using either of those alternatives, I decided to try a little trick that I remembered seeing in BOL when reading about the specific properties supported by XMLA.

Open up your Analysis Services data source and click the 'All' button on the left to go into the properties.



In the text field provided for the 'Extended Properties' property enter 'Format=Tabular' and save your changes.



You will get a warning, one for each column, where SSIS will complain about not being able to map the data type and saying it will use DT_WSTR data type instead. This is fine; it appears SSIS is unable to automatically derive data types from an MDX query.

To fix this, all you have to do is convert your data types using a Derived Columns task. You may have to get a little creative when converting some of the numeric values coming out of your MDX queries from text to the appropriate SQL Server data type if they are in expressed using scientific notation, for example. Maybe I will blog about that another time.

Hopefully this will save you some headaches and allow you to query a cube from the data flow without having to use a linked server or a script task.