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

Question

0

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.

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.

Find example of working and non-working expression below:

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

            ]
        }')

Answer 1

Hi Lasse,

Thank you for posting query in Microsoft Q&A Platform. Usually this kind of error comes when something wrong with expressions or data in expressions.

Are you storing this json in to some variable or parameter and then passing it to Mappings? If yes, kindly not do that, direct pass it to mappings. For example, lets assume you are getting this dynamic mapping from file using lookup activity, then directly try to pass it in mappings and see if that helps.

I am suspecting, there may be some length limit for variables or parameters and that may causing this issue. Kindly try above and let me know how it goes.