ADF Pipeline Copy data "BadRequest" error - SQL to Parquet using expression builder for Mapping
I get a very non-explanatory error running this pipeline: { "code": "BadRequest", "message": null, "target": "pipeline//runid/xxx", "details": null, "error": null }
Environment: Azure Synapse Analytics (ADF v2)
Activity: Copy data
Source: SQL Server and table with approx. 95 columns - with Parquet incompatible characters like white space and ()
Sink: Datalake gen2 Parquet
Mapping: Dynamic with attached (reduced columns to fulfill body limit in post) expression to "rename" columns to Parquet compatible naming.
Any good ideas? I am giving up finding an answer on the different fora :(
Problem-solving: tried to reduce amount of columns and adding few at the time to find the column triggering the error.
Conclusion: few columns works fine, at column 25 it fails, but nothing wrong with json-object, tried to move number 25 to 24 and run 24 with success and move earlier to 25 - it keeps failing when there are 25 or more columns. But I can't find any information if there are limits in the expression builder or other in terms of json-doc and "manual" mapping.
Info: the pipeline works fine if sink is csv and schema is imported.
Working expression with 24 columns:
@json('{
"type": "TabularTranslator",
"mappings": [
{
"source": {
"name": "timestamp"
},
"sink": {
"name": "timestamp"
}
},
{
"source": {
"name": "Entry No_",
"type": "Int32"
},
"sink": {
"name": "Entry_No_",
"type": "Int32"
}
},
{
"source": {
"name": "Item No_",
"type": "String"
},
"sink": {
"name": "Item_No_",
"type": "String"
}
},
{
"source": {
"name": "Posting Date",
"type": "DateTime"
},
"sink": {
"name": "Posting_Date",
"type": "DateTime"
}
},
{
"source": {
"name": "Item Ledger Entry Type",
"type": "Int32"
},
"sink": {
"name": "Item_Ledger_Entry_Type",
"type": "Int32"
}
},
{
"source": {
"name": "Source No_",
"type": "String"
},
"sink": {
"name": "Source_No_",
"type": "String"
}
},
{
"source": {
"name": "Document No_",
"type": "String"
},
"sink": {
"name": "Document_No_",
"type": "String"
}
},
{
"source": {
"name": "Description",
"type": "String"
},
"sink": {
"name": "Description",
"type": "String"
}
},
{
"source": {
"name": "Location Code",
"type": "String"
},
"sink": {
"name": "Location_Code",
"type": "String"
}
},
{
"source": {
"name": "Inventory Posting Group",
"type": "String"
},
"sink": {
"name": "Inventory_Posting_Group",
"type": "String"
}
},
{
"source": {
"name": "Source Posting Group",
"type": "String"
},
"sink": {
"name": "Source_Posting_Group",
"type": "String"
}
},
{
"source": {
"name": "Item Ledger Entry No_",
"type": "Int32"
},
"sink": {
"name": "Item_Ledger_Entry_No_",
"type": "Int32"
}
},
{
"source": {
"name": "Valued Quantity",
"type": "Decimal"
},
"sink": {
"name": "Valued_Quantity",
"type": "Decimal"
}
},
{
"source": {
"name": "Item Ledger Entry Quantity",
"type": "Decimal"
},
"sink": {
"name": "Item_Ledger_Entry_Quantity",
"type": "Decimal"
}
},
{
"source": {
"name": "Invoiced Quantity",
"type": "Decimal"
},
"sink": {
"name": "Invoiced_Quantity",
"type": "Decimal"
}
},
{
"source": {
"name": "Cost per Unit",
"type": "Decimal"
},
"sink": {
"name": "Cost_per_Unit",
"type": "Decimal"
}
},
{
"source": {
"name": "Sales Amount (Actual)",
"type": "Decimal"
},
"sink": {
"name": "Sales_Amount__Actual_",
"type": "Decimal"
}
},
{
"source": {
"name": "Salespers__Purch_ Code",
"type": "String"
},
"sink": {
"name": "Salespers__Purch__Code",
"type": "String"
}
},
{
"source": {
"name": "Discount Amount",
"type": "Decimal"
},
"sink": {
"name": "Discount_Amount",
"type": "Decimal"
}
},
{
"source": {
"name": "User ID",
"type": "String"
},
"sink": {
"name": "User_ID",
"type": "String"
}
},
{
"source": {
"name": "Source Code",
"type": "String"
},
"sink": {
"name": "Source_Code",
"type": "String"
}
},
{
"source": {
"name": "Applies-to Entry",
"type": "Int32"
},
"sink": {
"name": "Applies-to_Entry",
"type": "Int32"
}
},
{
"source": {
"name": "Global Dimension 1 Code",
"type": "String"
},
"sink": {
"name": "Global_Dimension_1_Code",
"type": "String"
}
},
{
"source": {
"name": "Global Dimension 2 Code",
"type": "String"
},
"sink": {
"name": "Global_Dimension_2_Code",
"type": "String"
}
}
]
}')
Non-working expression with 25 columns:
@json('{
"type": "TabularTranslator",
"mappings": [
{
"source": {
"name": "timestamp"
},
"sink": {
"name": "timestamp"
}
},
{
"source": {
"name": "Entry No_",
"type": "Int32"
},
"sink": {
"name": "Entry_No_",
"type": "Int32"
}
},
{
"source": {
"name": "Item No_",
"type": "String"
},
"sink": {
"name": "Item_No_",
"type": "String"
}
},
{
"source": {
"name": "Posting Date",
"type": "DateTime"
},
"sink": {
"name": "Posting_Date",
"type": "DateTime"
}
},
{
"source": {
"name": "Item Ledger Entry Type",
"type": "Int32"
},
"sink": {
"name": "Item_Ledger_Entry_Type",
"type": "Int32"
}
},
{
"source": {
"name": "Source No_",
"type": "String"
},
"sink": {
"name": "Source_No_",
"type": "String"
}
},
{
"source": {
"name": "Document No_",
"type": "String"
},
"sink": {
"name": "Document_No_",
"type": "String"
}
},
{
"source": {
"name": "Description",
"type": "String"
},
"sink": {
"name": "Description",
"type": "String"
}
},
{
"source": {
"name": "Location Code",
"type": "String"
},
"sink": {
"name": "Location_Code",
"type": "String"
}
},
{
"source": {
"name": "Inventory Posting Group",
"type": "String"
},
"sink": {
"name": "Inventory_Posting_Group",
"type": "String"
}
},
{
"source": {
"name": "Source Posting Group",
"type": "String"
},
"sink": {
"name": "Source_Posting_Group",
"type": "String"
}
},
{
"source": {
"name": "Item Ledger Entry No_",
"type": "Int32"
},
"sink": {
"name": "Item_Ledger_Entry_No_",
"type": "Int32"
}
},
{
"source": {
"name": "Valued Quantity",
"type": "Decimal"
},
"sink": {
"name": "Valued_Quantity",
"type": "Decimal"
}
},
{
"source": {
"name": "Item Ledger Entry Quantity",
"type": "Decimal"
},
"sink": {
"name": "Item_Ledger_Entry_Quantity",
"type": "Decimal"
}
},
{
"source": {
"name": "Invoiced Quantity",
"type": "Decimal"
},
"sink": {
"name": "Invoiced_Quantity",
"type": "Decimal"
}
},
{
"source": {
"name": "Cost per Unit",
"type": "Decimal"
},
"sink": {
"name": "Cost_per_Unit",
"type": "Decimal"
}
},
{
"source": {
"name": "Sales Amount (Actual)",
"type": "Decimal"
},
"sink": {
"name": "Sales_Amount__Actual_",
"type": "Decimal"
}
},
{
"source": {
"name": "Salespers__Purch_ Code",
"type": "String"
},
"sink": {
"name": "Salespers__Purch__Code",
"type": "String"
}
},
{
"source": {
"name": "Discount Amount",
"type": "Decimal"
},
"sink": {
"name": "Discount_Amount",
"type": "Decimal"
}
},
{
"source": {
"name": "User ID",
"type": "String"
},
"sink": {
"name": "User_ID",
"type": "String"
}
},
{
"source": {
"name": "Source Code",
"type": "String"
},
"sink": {
"name": "Source_Code",
"type": "String"
}
},
{
"source": {
"name": "Applies-to Entry",
"type": "Int32"
},
"sink": {
"name": "Applies-to_Entry",
"type": "Int32"
}
},
{
"source": {
"name": "Global Dimension 1 Code",
"type": "String"
},
"sink": {
"name": "Global_Dimension_1_Code",
"type": "String"
}
},
{
"source": {
"name": "Global Dimension 2 Code",
"type": "String"
},
"sink": {
"name": "Global_Dimension_2_Code",
"type": "String"
}
},
{
"source": {
"name": "Source Type",
"type": "Int32"
},
"sink": {
"name": "Source_Type",
"type": "Int32"
}
}
]
}')