I was unable to load MDX query with parameter into an SSIS package. Steps followed my me are by adding dataflow->OLEDB source with Connection manager as Microsoft Analysis Service and choosing the option SQL command and pasted my query. SSIS is unable to parse the query as it is in MDX and if i remove the parameter i am able to parse with errors which can be solved by following some steps found in the following article http://www.sqlservercentral.com/articles/MDX/64697/ I would like to know if there is any way to load the MDX query with parameter in SSIS. Thanks

Here is some MDX that uses paramters. When using paramters in MDX you have to enclose the entire statement in quotes. This MDX query uses two parameters: Time and business entity. ="WITH MEMBER [Measures].[FundingRatio] AS '([Measures].[Signeddata], [Account].[Funding Volume Loan Count] ) / [Measures].[AvgSubs]' SELECT { ADDCALCULATEDMEMBERS ([Measures].Members) } ON COLUMNS , {{" & parameters!pTime.value & " } * { [Account].[Submission Units] }} ON ROWS FROM [MyCube] WHERE ( [Location].[TotalLocations], [Category].[ABC], " & parameters!pEntity.value & ")" Let me know is this solves your problem.Please mark as answer if this helps. Thank you. http://thebipalace.wordpress.com

Hi, To get the issue around, i would suggest you add Data Flow Transformations component like Lookup to filter the mdx result. Additionally, submit a feedback at https://connect.microsoft.com/SQLServer/Feedback and hope microsoft enable such fetures on future products. thanks, Jerry

you can use script task for the same.. check this, http://quest4gen.blogspot.com/2010/05/load-cube-date-to-sql-table-using.html Let us TRY this | My Blog :: http://quest4gen.blogspot.com/

Hey guys I am able to run the query and load the data but can you guys help me in understanding the cleansing part where it converts exponential to decimal in the article that i have posted. Thanks