Configuring an MDX query on SSIS

Question

Hi I am having troubles configuring the SSIS task to run an MDX query. The parse works fine but it doesn't allow me to display the different columns of the query to map it

Here is the query i used:

SELECT [Measures].[# Consumers] ON 0,
      [Company].[Company Country Code].[Company Country Code].MEMBERS ON 1
FROM _CDM

The Error thrown is:

No Column information was returned by the SQL Command

Error snapshot

Answer 1

You can use MDX Select as a Source in Data Transformation Task.
Two important notes:

• Use MS OLE DB Provider for Analysis Services, configure it for your SSAS DB
• In OLE DB Provider for AS, go to All Properties Tab, select Advanced section and type Format=Tabular for Extended Properties.

In this case, at OLE DB Source Editor you can input your MDX query.
Important - Preview button might not work, you should check query metadata switching to Columns tab.