CODEWITHSUNDEEP
ssisssasmdx
user3486773
user3486773

Reputation: 1246

MDX query not returning rows in SSIS data flow task?

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?

Upvotes: 0

Views: 827

Answers (1)

Ferdipux
Ferdipux

Reputation: 5256

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.

Upvotes: 0

Related Questions