SQL task MDX with parameters. SSIS

Question

I need to create dynamically some local cubes in ssis and i don't know how to use parameters in this query

CREATE GLOBAL CUBE test
STORAGE 'C:\test.cub'
FROM Cube_test( 
Measure [Cube_test].[Val], 
dimension [Cube_test].[Date_1] ( 
level [Date_1],     
member [Date_1].[Month].&[2],
member [Date_1].[Month].&[?] )
 )

I can run the sql task without parameters but when i try to run the sql task with parameters give me the error "incorrect parameter". With sql query i can use them but not with mdx

try to create it as an expression instead of using the '?' – mxix Mar 08 '19 at 10:03 — mxix, Mar 08 '19 at 10:03

userfl89 · Answer 1 · 2019-03-08T16:50:01.150

Use a variable as the SQL source statement instead and create a string variable with an expression that combines the MDX statement with the parameter for the query. This can be done by enclosing the MDX in double-quotes as follows. The \ will need to be escaped as done below, but only a single \ will be in the result when it's parsed. To properly concatenate a numeric parameter into the statement, it must be cast to a string which is done with the (DT_STR,length,code page) function. I'm guessing the length of the parameter will be no longer than 2 digits since it's a month, thus 2 is used for the length parameter of the DT_STR function and you can adjust this as needed. The following can be used as the expression of a string SSIS variable, which can then be used as the SQL command for the Execute SQL Task. On the Execute SQL Task, change the SQLSourceType property to Variable and select the variable with this expression as the SourceVariable. You'll also want to set DelayValidation to true on the Properties window (press F4) of the Execute SQL Task. If an SSIS variable is used as the MDX parameter instead of an SSIS parameter as in the example below, change @[$Package::ParameterName] to the variable name, for example @[User::VariableName]. If it's a project parameter then Package can be replaced with Project in the parameter name, with the $ kept , i.e. @[$Project::ParameterName].

"CREATE GLOBAL CUBE test
STORAGE 'C:\\test.cub'
FROM Cube_test( 
Measure [Cube_test].[Val], 
dimension [Cube_test].[Date_1] ( 
level [Date_1],     
member [Date_1].[Month].&[2],
member [Date_1].[Month].&[" + (DT_STR, 2, 1252) @[$Package::ParameterName] + "] ) )"

SQL task MDX with parameters. SSIS

1 Answers1