My question is: What would be the correct syntax to add 2 or more tables to an existing database using xmla file and SSMS? I have a model deployed to Azure Analysis Services server. So the database is already created. I want to create or replace tables by running one single xmla script in SSMS.
When I use the script below, to create one table, it works perfectly. But what I need is to create several tables (not only one) using one single xmla script.
Script that works for one table
The script below works perfectly and correctly "creates or replaces" one table on the database.
{
"createOrReplace": {
"object": {
"database": "MyDatabase",
"table": "MyTable"
},
"table": {
"name": "MyTable",
"columns": [
{
"name": "MyTableId",
"dataType": "int64",
"sourceColumn": "MyTableId"
},
{
"name": "MyTable",
"dataType": "string",
"sourceColumn": "MyTable"
}
],
"partitions": [
{
"name": "Partition",
"dataView": "full",
"source": {
"type": "m",
"expression": [
"let",
" Source=GetFileList(),",
" #\"MyTable txt\" = Source{[Name=\"MyTable.txt\"]}[Content],",
" #\"Imported CSV\" = Csv.Document(#\"MyTable txt\",[Delimiter=\",\", Columns=9, Encoding=1252, QuoteStyle=QuoteStyle.None]),",
" #\"Promoted Headers\" = Table.PromoteHeaders(#\"Imported CSV\", [PromoteAllScalars=true]),",
" #\"Changed Type\" = Table.TransformColumnTypes(#\"Promoted Headers\",{{\"MyTableId\", Int64.Type}, {\"MyTable\", type text}, {\"Description\", type text}}),",
" #\"Removed Columns\" = Table.RemoveColumns(#\"Changed Type\",{\"Description\"})",
"in",
" #\"Removed Columns\""
]
}
}
]
}
}
}
I tried the code below to add 2 tables but got an error The JSON DDL request failed with the following error: Unrecognized JSON property: tables. Check path 'tables', line 6, position 16..
{
"createOrReplace": {
"database": {
"name": "MyDatabase",
"tables": [
{"name": "TableA",
"columns": [
{
"name": "TableAId",
"dataType": "int64",
"sourceColumn": "TableAId"
},
{
"name": "TableA",
"dataType": "string",
"sourceColumn": "TableA"
}
],
"partitions": [
{
"name": "Partition",
"dataView": "full",
"source": {
"type": "m",
"expression": [
"let",
" Source=GetFileList(),",
" #\"TableA txt\" = Source{[Name=\"TableA.txt\"]}[Content],",
" #\"Imported CSV\" = Csv.Document(#\"TableA txt\",[Delimiter=\",\", Columns=9, Encoding=1252, QuoteStyle=QuoteStyle.None]),",
" #\"Promoted Headers\" = Table.PromoteHeaders(#\"Imported CSV\", [PromoteAllScalars=true]),",
" #\"Changed Type\" = Table.TransformColumnTypes(#\"Promoted Headers\",{{\"TableAId\", Int64.Type}, {\"TableA\", type text}, {\"Description\", type text}}),",
" #\"Removed Columns\" = Table.RemoveColumns(#\"Changed Type\",{\"Description\"})",
"in",
" #\"Removed Columns\""
]
}
}
] },
{"name": "TableB",
"columns": [
{
"name": "TableBId",
"dataType": "int64",
"sourceColumn": "TableBId"
},
{
"name": "TableB",
"dataType": "string",
"sourceColumn": "TableB"
}
],
"partitions": [
{
"name": "Partition",
"dataView": "full",
"source": {
"type": "m",
"expression": [
"let",
" Source=GetFileList(),",
" #\"TableB txt\" = Source{[Name=\"TableB.txt\"]}[Content],",
" #\"Imported CSV\" = Csv.Document(#\"TableB txt\",[Delimiter=\",\", Columns=11, Encoding=1252, QuoteStyle=QuoteStyle.None]),",
" #\"Promoted Headers\" = Table.PromoteHeaders(#\"Imported CSV\", [PromoteAllScalars=true]),",
" #\"Changed Type\" = Table.TransformColumnTypes(#\"Promoted Headers\",{{\"TableBId\", Int64.Type}, {\"TableB\", type text}, {\"Description\", type text}}),",
" #\"Removed Columns\" = Table.RemoveColumns(#\"Changed Type\",{\"Description\"})",
"in",
" #\"Removed Columns\""
]
}
}
] }
]
}
}
}
I also tried the code below but got error Error -1055784777: The JSON DDL request failed with the following error: Unrecognized JSON property: tables. Check path 'createOrReplace.tables', line 6, position 14.. The JSON DDL request failed with the following error: Unrecognized JSON property: tables. Check path 'createOrReplace.tables', line 6, position 14..
{
"createOrReplace": {
"object": {
"database": "MyDatabase"
},
"tables": [
{
"name": "TableA",
"columns": [
{
"name": "TableAId",
"dataType": "int64",
"sourceColumn": "TableAId"
},
{
"name": "TableA",
"dataType": "string",
"sourceColumn": "TableA"
}
],
"partitions": [
{
"name": "Partition",
"dataView": "full",
"source": {
"type": "m",
"expression": [
"let",
" Source=GetFileList(),",
" #\"TableA txt\" = Source{[Name=\"TableA.txt\"]}[Content],",
" #\"Imported CSV\" = Csv.Document(#\"TableA txt\",[Delimiter=\",\", Columns=9, Encoding=1252, QuoteStyle=QuoteStyle.None]),",
" #\"Promoted Headers\" = Table.PromoteHeaders(#\"Imported CSV\", [PromoteAllScalars=true]),",
" #\"Changed Type\" = Table.TransformColumnTypes(#\"Promoted Headers\",{{\"TableAId\", Int64.Type}, {\"TableA\", type text}, {\"Description\", type text}}),",
" #\"Removed Columns\" = Table.RemoveColumns(#\"Changed Type\",{\"Description\"})",
"in",
" #\"Removed Columns\""
]
}
}
]
},
{
"name": "TableB",
"columns": [
{
"name": "TableBId",
"dataType": "int64",
"sourceColumn": "TableBId"
},
{
"name": "TableB",
"dataType": "string",
"sourceColumn": "TableB"
}
],
"partitions": [
{
"name": "Partition",
"dataView": "full",
"source": {
"type": "m",
"expression": [
"let",
" Source=GetFileList(),",
" #\"TableB txt\" = Source{[Name=\"TableB.txt\"]}[Content],",
" #\"Imported CSV\" = Csv.Document(#\"TableB txt\",[Delimiter=\",\", Columns=11, Encoding=1252, QuoteStyle=QuoteStyle.None]),",
" #\"Promoted Headers\" = Table.PromoteHeaders(#\"Imported CSV\", [PromoteAllScalars=true]),",
" #\"Changed Type\" = Table.TransformColumnTypes(#\"Promoted Headers\",{{\"TableBId\", Int64.Type}, {\"TableB\", type text}, {\"Description\", type text}}),",
" #\"Removed Columns\" = Table.RemoveColumns(#\"Changed Type\",{\"Description\"})",
"in",
" #\"Removed Columns\""
]
}
}
]
}
]
}
}
Use the sequence command:
{
"sequence": {
"operations": [
{ CREATE TABLE 1},
{ CREATE TABLE 2}
]
}
}
You can include as many operations as you want.