How to run multiple XMLA statements in the same script

Question

Hi,

I'm working on a script to create around 50 roles for a Tabular Model, but have to troubles figuring out how I can run them all at once.

The script looks like below, just only with 2 roles as an example -

{
"createOrReplace": {
"object": {
"database": "Test",
"role": "Group A"
},
"role": {
"name": "Group A",
"modelPermission": "read",
"members": [
{
"memberName": "Domain\\xxx",
"memberId": "S-......-73405"
}
],
"tablePermissions": [
{
"name": "Tablename",
"filterExpression": "'TableName'[HomeDepartment] IN ({\"XX\",\"YY\"}) && 'TableName'[HomeUnit] IN ({\"ZZ\",\"WW\"})"
}
]
}
}
}

// Group B

"createOrReplace": {
"object": {
"database": "Test",
"role": "Group B"
},
"role": {
"name": "Group B",
"modelPermission": "read",
"members": [
{
"memberName": "Domain\GGG",
"memberId": "S.......-53912"
},
{
"memberName": "Domain\AAA",
"memberId": "S......-26483"
}
],
"tablePermissions": [
{
"name": "TableName",
"filterExpression": "'TableName'[HomeDepartment] IN ({\"HH\",\"KK\",\"TT\",\"FF\",\"MM\"}) && 'TableName'[HomeUnit] IN ({\"EE\"})"
}
]
}
}

I can run each "createOrReplace" section individually and it works fine and create the roles with permissions with out any problems but if I try to run the whole script (hit F5 in SSMS) I get the error -
Executing the query ...
The JSON DDL request failed with the following error: Additional text encountered after finished reading JSON content: {. Path '', line 30, position 1..
Run complete

I have then tried to add <Batch Transaction="true" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"> ..... </Batch> around the script, but then I get the error - The { text node at line 9, column 3 cannot appear inside the Batch element (namespace http://schemas.microsoft.com/analysisservices/2003/engine) under Envelope/Body/Execute/Command. This element can only have text nodes containing white-space characters.

I have also tried other combinations/suggestions, but none of them works.

My experience running XMLA scripts are fairly limited, so I hope some one can help on how I can fix this? I can of course always just run each individual step to get the roles created/updated but it would be nice just to be able to execute the whole script at once.

Regards
Steen

Answer 1

I think you might need to use the sequence command to run multiple operations in the one batch

Answer 2

Glad you have resolve the issue, if you have further issue, feel free to post in the forum.