7

enter image description hereHi 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

enter image description here

Cherif Ghattas
  • 71
  • 1
  • 3
  • before asking your first question is it better to read the [Tour Page](https://www.stackoverflow.com/tour) to learn more on asking good question and other helpful informations, also to get your first `informed` badge – Hadi Jun 16 '17 at 15:13
  • 1
    Hadi, could you please be more precise? His question seems to be good. – Arnaud Gastelblum Jun 16 '17 at 16:12
  • You might need to use Script Component as Source for this. MDX won't run as a SQL Command – Jigar Jun 16 '17 at 17:34
  • 1
    @ArnaudGastelblum take a look at the initial post before my edits. I edited the question to become good :) https://stackoverflow.com/posts/44592041/revisions – Hadi Jun 16 '17 at 22:29
  • @Cherif what is the connection provider you are using? Oledb provider for analysis services? Or sql server provider? – Hadi Jun 16 '17 at 22:40
  • Hi Hadi, we're using OleDB for analysis services – Cherif Ghattas Jun 19 '17 at 09:39

1 Answers1

7

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.

AS OLE DB Data Source
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.

Ferdipux
  • 5,116
  • 1
  • 19
  • 33
  • Hi, thank you for your answer, we're using a multidimensional cube and we have even tried the tabular configuration but it didn't work – Cherif Ghattas Jun 19 '17 at 09:42
  • @CherifGhattas, could you share your settings of OLEDB provider for SSAS? And have you tested your query in SSMS - does it return any data? – Ferdipux Jun 19 '17 at 09:48
  • Hi Ferdipux, I added the snapshot for the SSAS configuration at the beginning of my post. I confirm we manage to run the MDX query on the SSMS. Thank you – Cherif Ghattas Jun 19 '17 at 10:33
  • @CherifGhattas, at your screenshots I do not see `Format=Tabular` among *Extended Properties*. Please set it. Another important point - *Preview* button might not work, after you enter MDX query text, switch to *Columns* tab and check column names there. – Ferdipux Jun 20 '17 at 07:21
  • @Fredipux may I wasn' clear but it is a multidimentionnal cube and not TABULAR Thank you – Cherif Ghattas Jun 30 '17 at 10:18
  • 1
    @CherifGhattas, you have to add configuration parameter `Format=Tabular` for a **multidimensional cube** connection! That is the point. – Ferdipux Jul 03 '17 at 17:52