MDX query not returning rows in SSIS data flow task?

Question

So I am browsing a SSAS cube in SSMS and using query designer to build my query. I am only wanting to return dimensions and not measures. Here are my steps:

I right click the cube and select browse. I then drag over the dimensions I want and do not get any results obviously, because I have not selected any measures. So I then click the 'show empty cells' option, and bam, there is everything I want!!!

From this point I click 'Design Mode' button to reveal the query. The issue is, when I put this query into a data flow task in SSIS, it doesn't return any rows.

Here is what I have :

   SELECT { } ON COLUMNS, { ([Customer].[Customer].[Customer Number].ALLMEMBERS ) } 
   DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [Customer] 
   CELL PROPERTIES VALUE

When I run this in SSMS, I get rows. When I run in SSIS, I get nothing. How can I return the same rows in SSIS as in SSMS?

Ferdipux Ferdipux · Accepted Answer · 2017-12-25T09:16:08

Problem with your query is that it has zero columns - SELECT { } ON COLUMNS. This confuses SSIS - it gets zero column dataset. SSIS has to know dataset, its columns and properties like data type, encoding etc. Your query provides no columns and SSIS cannot use it; although it is Ok from MDX syntax point of view.

You need to change your query to return some columns, like this (presuming you have hierarchy on [Customer].[Customer].[Customer Number])

WITH MEMBER [Measures].[Caption] AS [Customer].[Customer].CURRENTMEMBER.MEMBER_CAPTION  
MEMBER [Measures].[U_Name] AS [Customer].[Customer].CURRENTMEMBER.UNIQUE_NAME
SELECT { [Measures].[Caption], [Measures].[U_Name] } ON COLUMNS, 
    { ([Customer].[Customer].[Customer Number].ALLMEMBERS ) } ON ROWS
FROM [Customer]

This query returns two columns - Caption and U_Name.

MDX query not returning rows in SSIS data flow task?

1 Answers