ADF Pipeline Copy data "BadRequest" error - SQL to Parquet using expression builder for Mapping

Lasse 0 Reputation points
2023-02-27T07:50:06.19+00:00

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"
                    }
                }

            ]
        }')
C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
10,209 questions
0 comments No comments
{count} votes