none
How can I pass a mdx query in ssis execute sql task

    Question

  • Hi,

    I have added this mdx query in the exceute sql task:

    SELECT 
    NON EMPTY { [Measures].[Sum of Quantity] } ON COLUMNS as quantity, 
    NON EMPTY { ([SalesHistory].[SSAS_REFRESH_DATE].[SSAS_REFRESH_DATE].ALLMEMBERS ) as cube_date }  ON ROWS FROM [Model] 

    But it is giving me an error while running the package:

    [Execute SQL Task] Error: An error occurred while assigning a value to variable "var2": "Unable to find column [SalesHistory].[SSAS_REFRESH_DATE] in the result set.".

    Monday, May 14, 2018 2:25 PM

All replies

  • Hi,

    Change your query to :

    WITH
    MEMBER [Measures].[ParameterCaption] AS [SalesHistory].[SSAS_REFRESH_DATE].CURRENTMEMBER.MEMBER_CAPTION 
    
    
    SELECT 
    NON EMPTY ( [Measures].[Sum of Quantity], [Measures].[ParameterCaption]) ON COLUMNS as quantity, 
    NON EMPTY { ([SalesHistory].[SSAS_REFRESH_DATE].[SSAS_REFRESH_DATE].ALLMEMBERS ) as cube_date }  ON ROWS FROM [Model] 

    In the result set of execute SQL task give the result name as [Measures].[ParameterCaption] and assign the value to a variable.


    Ousama EL HOR

    [If a post helps to resolve your issue, please click the "Mark as Answer"  of that post or click Answered"Vote as helpful"  button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]


     [User Page]     [MSDN Page]     [Blog]     [Linkedin]


    Monday, May 14, 2018 2:29 PM
  • Hi harshita1612,

    Thanks for your question.

    >>>How can I pass a mdx query in ssis execute sql task
    Please refer to below step by step blog talking about this similar issue:
    https://www.fourmoo.com/2014/04/03/ssis-using-sql-server-analysis-services-ssas-mdx-query-into-variable-in-execute-sql-task/
    https://bisherryli.com/2012/08/14/ssis-113-querying-ssas-cube-with-mdx-from-ssis/


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, May 15, 2018 2:25 AM
    Moderator
  • Hi,

    I removed the parameter mapping and after this it is giving me this error:

    [Execute SQL Task] Error: Executing the query "WITH
    MEMBER [Measures].[ParameterCaption] AS [Sale..." failed with the following error: "Query (6, 85) The syntax for 'as' is incorrect.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

    Tuesday, May 15, 2018 8:14 AM
  • Hi harshita1612,

    Thanks for your response.

    >>>I removed the parameter mapping and after this it is giving me this error:The syntax for 'as' is incorrect
    SSAS MDX does not support to use AS like T-SQL query, please remove as quantity and as cube_date from your MDX query:

    SELECT 
    NON EMPTY { [Measures].[Sum of Quantity] } ON COLUMNS, 
    NON EMPTY { [SalesHistory].[SSAS_REFRESH_DATE].[SSAS_REFRESH_DATE].ALLMEMBERS }  ON ROWS 
    FROM [Model]  



    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, May 15, 2018 8:22 AM
    Moderator
  • Hi,

    I am using this query now:

    WITH

    MEMBER [Measures].[ParameterCaption] as [SalesHistory].[SSAS_REFRESH_DATE].CURRENTMEMBER.MEMBER_CAPTION 


    SELECT 
    NON EMPTY ( [Measures].[Sum of Quantity], [Measures].[ParameterCaption]) ON 0 as quantity, 
    NON EMPTY { ([SalesHistory].[SSAS_REFRESH_DATE].[SSAS_REFRESH_DATE].ALLMEMBERS ) }  ON 1 as cube_date FROM [Model] 

    Still i am getting the error:

    [Execute SQL Task] Error: Executing the query "WITH
    MEMBER [Measures].[ParameterCaption] as [Sale..." failed with the following error: "Query (6, 79) The syntax for 'as' is incorrect.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

    Tuesday, May 15, 2018 8:55 AM
  • Hi,

    You can't have "As" in "ON 0 AS quantity, it is not like T-SQL.

    You have to remove the AS, the names of columns are the names of the measures :

    WITH

    MEMBER [Measures].[ParameterCaption] as [SalesHistory].[SSAS_REFRESH_DATE].CURRENTMEMBER.MEMBER_CAPTION 


    SELECT 
    NON EMPTY ( [Measures].[Sum of Quantity], [Measures].[ParameterCaption]) ON 0 , 
    NON EMPTY { ([SalesHistory].[SSAS_REFRESH_DATE].[SSAS_REFRESH_DATE].ALLMEMBERS ) }  ON 1  FROM [Model] 


    Ousama EL HOR

    [If a post helps to resolve your issue, please click the "Mark as Answer"  of that post or click Answered"Vote as helpful"  button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]


     [User Page]     [MSDN Page]     [Blog]     [Linkedin]


    Tuesday, May 15, 2018 9:04 AM
  • i changed the query like the one given above but still facing the error:

    [Execute SQL Task] Error: Executing the query "WITH
    MEMBER [Measures].[ParameterCaption] as [Sale..." failed with the following error: "Query (6, 11) The 'Measures' hierarchy appears more than once in the tuple.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

    Tuesday, May 15, 2018 9:34 AM
  • Hi harshita1612,

    Thanks for your response. 

    Try below MDX query:

    WITH
    MEMBER [Measures].[ParameterCaption] as 
    [SalesHistory].[SSAS_REFRESH_DATE].CURRENTMEMBER.MEMBER_CAPTION 
    SELECT 
    NON EMPTY { [Measures].[Sum of Quantity], 
              [Measures].[ParameterCaption] } ON 0 , 
    NON EMPTY 
    { [SalesHistory].[SSAS_REFRESH_DATE].[SSAS_REFRESH_DATE].ALLMEMBERS  }  ON 1
      FROM [Model]



    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, May 15, 2018 9:43 AM
    Moderator
  • Hi,

    Thank you for the response!!

    Still getting this error:

    [Execute SQL Task] Error: An error occurred while assigning a value to variable "var2": "Unable to find column  [SalesHistory].[SSAS_REFRESH_DATE] in the result set.".

    I am using this in the result set tab.


    Tuesday, May 15, 2018 10:04 AM
  • Change the result Name to [Measures].[ParameterCaption].

    Ousama EL HOR

    [If a post helps to resolve your issue, please click the "Mark as Answer"  of that post or click Answered "Vote as helpful"  button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]


     [User Page]     [MSDN Page]     [Blog]     [Linkedin]

    Tuesday, May 15, 2018 10:05 AM
  • Still getting the error

    [Execute SQL Task] Error: Executing the query "SELECT 
    NON EMPTY { [Measures].[Sum of Quantity], ..." failed with the following error: "Query (3, 11) The '[ParameterCaption]' member was not found in the cube when the string, [Measures].[ParameterCaption], was parsed.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

    Tuesday, May 15, 2018 10:08 AM
  • Hi harshita1612,

    Thanks for your response. 

    >>>Query (3, 11) The '[ParameterCaption]' member was not found in the cube when the string, [Measures].[ParameterCaption], was parsed.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

    According to your above error messages, it seems that you still facing syntax error with your MDX query. Please test the MDX query in SSMS first, see if it can reproduce the error message you got.


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, May 16, 2018 1:20 AM
    Moderator
  • The other issue that can cause this error is the fact that you have the NON EMPTY keywords on both your axis. So if your measure is empty you will return an empty cellset with no rows or columns and SSIS will throw this error. When running queries in SSIS you should almost never use NON EMPTY on your column axis (axis 0)

    So try the following:

    SELECT 
    { [Measures].[Sum of Quantity] } ON 0 , 
    NON EMPTY { [SalesHistory].[SSAS_REFRESH_DATE].[SSAS_REFRESH_DATE].ALLMEMBERS  }  ON 1
    FROM [Model]


    http://darren.gosbell.com - please mark correct answers

    • Proposed as answer by alexander fun Wednesday, May 16, 2018 5:24 AM
    Wednesday, May 16, 2018 4:02 AM
    Moderator
  • Hi Alexander,

    I ran your query and passed this in the resultset but still giving an error.

    [Execute SQL Task] Error: An error occurred while assigning a value to variable "var2": "Unable to find column ssas_refresh_date in the result set.".

    Wednesday, May 16, 2018 4:44 PM
  • Hi Wilson,

    I have tested this query in Report Builder and it is working fine over there.

    Wednesday, May 16, 2018 4:45 PM
  • Hi,

    I am using this query now for getting the latest refresh date out of the cube

    SELECT TOP 1 [LAST_DATA_UPDATE] as last_date
    FROM $System.MDSCHEMA_CUBES
    WHERE [catalog_name] = 'xx'
    ORDER BY [LAST_DATA_UPDATE] DESC ;

    But here the date format is 

    last_date
    5/17/2018 5:13:15 AM

    I need it as 5/17/2018 5:13:15 

    I tried to use format but it is not working for me.

    So can someone please help..i really need help on this

    Thursday, May 17, 2018 9:42 AM
  • SELECT TOP 1 
     CONVERT(VARCHAR(10), [LAST_DATA_UPDATE], 101) 
                            + ' ' + CONVERT(VARCHAR(8), [LAST_DATA_UPDATE], 108) as last_date
    FROM $System.MDSCHEMA_CUBES
    WHERE [catalog_name] = 'xx'
    ORDER BY [LAST_DATA_UPDATE] DESC ;


    Ousama EL HOR

    [If a post helps to resolve your issue, please click the "Mark as Answer"  of that post or click Answered "Vote as helpful"  button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]


     [User Page]     [MSDN Page]     [Blog]     [Linkedin]

    Thursday, May 17, 2018 9:46 AM