How to pass a JSON payload in Azure Data Factory Data Flow? The payload format from Copy Transform throws parse error

Question

How to pass a JSON payload in Azure Data Factory Data Flow? The payload format from Copy Transform throws parse error

Gautam Warrier 5

Hello,

I have an API to which I can connect and retrieve data from, using the Copy activity/transform in Azure Data Factory. It takes a JSON payload in the request body.

{
     "filters": {
        "from":"2023-10-13T18:00:00.000000Z",        
        "to":"2023-10-13T18:59:59.999999Z"
    }
 }

However, since the response is a complex JSON which needs to be flattened multiple times, I'm trying to implement the same using Mapping Data Flow, instead of Copy.

For some reason, in the Mapping Data Flow the Source setting doesn't seem to process the JSON request body payload in the same way - it throws a generic parse error (pasted below). Is there a different/particular format in which the request body needs to be provided in the Data Flow?

Spark job failed: { "text/plain": "{\"runId\":\"22e7ce53-e63d-45c4-a4f4-4db42a05c26a\",\"sessionId\":\"b17813c2-1869-41a4-9802-e34c7272cf99\",\"status\":\"Failed\",\"payload\":{\"statusCode\":400,\"shortMessage\":\"com.microsoft.dataflow.broker.InvalidOperationException: DSL compilation failed: DF-DSL-001 - DSL stream has parsing errors\\nLine 4 Position 11: body: '\\nmismatched input ''' expecting {DECIMAL_LITERAL, HEX_LITERAL, OCT_LITERAL, BINARY_LITERAL, MAX_INT, MIN_INT, MAX_LONG, MIN_LONG, POSITIVE_INF, NEGATIVE_INF, '-', '!', '$', '~', ':', '(', '#', '[', '@(', '[]', FLOAT_LITERAL, HEX_FLOAT_LITERAL, STRING_LITERAL, REGEX_LITERAL, 'parameters', 'functions', 'stores', 'as', 'input', 'output', 'constant', 'expression', 'integer', 'short', 'long', 'double', 'float', 'decimal', 'boolean', 'timestamp', 'date', 'byte', 'binary', 'integral', 'number', 'fractional', 'any', IDENTIFIER, ANY_IDENTIFIER, META_MATCH, '$$', '$$$', '$#', OPEN_INTERPOLATE}\",\"detailedMessage\":\"Failure 2023-10-24 23:15:23.674 failed DebugManager.processJob, run=22e7ce53-e63d-45c4-a4f4-4db42a05c26a, errorMessage=com.microsoft.dataflow.broker.InvalidOperationException: DSL compilation failed: DF-DSL-001 - DSL stream has parsing errors\\nLine 4 Position 11: body: '\\nmismatched input ''' expecting {DECIMAL_LITERAL, HEX_LITERAL, OCT_LITERAL, BINARY_LITERAL, MAX_INT, MIN_INT, MAX_LONG, MIN_LONG, POSITIVE_INF, NEGATIVE_INF, '-', '!', '$', '~', ':', '(', '#', '[', '@(', '[]', FLOAT_LITERAL, HEX_FLOAT_LITERAL, STRING_LITERAL, REGEX_LITERAL, 'parameters', 'functions', 'stores', 'as', 'input', 'output', 'constant', 'expression', 'integer', 'short', 'long', 'double', 'float', 'decimal', 'boolean', 'timestamp', 'date', 'byte', 'binary', 'integral', 'number', 'fractional', 'any', IDENTIFIER, ANY_IDENTIFIER, META_MATCH, '$$', '$$$', '$#', OPEN_INTERPOLATE}\"}}\n" } - RunId: 22e7ce53-e63d-45c4-a4f4-4db42a05c26a

1 answer

Your answer

Answer 1

AnnuKumari-MSFT 34,566 Microsoft Employee Moderator

Hi Gautam Warrier ,

Welcome to Microsoft Q&A platform and thanks for posting your question here.

As per my understanding , you are getting an exception "DF-DSL-001 - DSL stream has parsing errors" while trying to process JSON body payload. Please let me know if that is not the correct understanding.

It seems that there is some issue with how the json is formatted while sending the request .

Kindly confirm if you are passing the request body as parameter in the dataflow?

Probably you need to use escape characters i.e. '' symbol so that it will pass the single quotes in proper format.

For more details, kindly check out the below video: Write dynamic expression for SQL with single quotes around parameter value in Mapping data flows

Hope it helps. Kindly accept the answer by clicking on Accept answer button. Thankyou.

AnnuKumari-MSFT 34,566 Reputation points Microsoft Employee Moderator

2023-10-26T08:27:27.6366667+00:00

Hi Gautam Warrier ,

Just checking in to see if the above answer helped. Please do consider clicking Accept Answer as accepted answers help community as well. Also, please click on Yes for the survey 'Was the answer helpful'
Gautam Warrier 5 Reputation points

2023-10-26T15:22:08.29+00:00

Hello Annu,

To clarify the implementation: I'm trying to pass the JSON payload in the Request Body field in the Source Options tab of the Data Mapping Flow. This throws the Parse error. However, when I use the same JSON payload in the Request Body of the Copy Activity in the pipeline, it works fine & retrieves data as expected (the only limitation is that because the response is a complex JSON Copy activity cannot unroll the nested arrays). I have attached the 2 screenshots below for reference. Hope that clarifies the issue.

Thanks.

Copy Activity screenshot which works:

Mapping Data Flow screenshot which throws the Parse error on passing the same JSON payload in the Request Body.
Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.
Gautam Warrier 5 Reputation points

2023-10-27T16:58:30.9+00:00

Hello AnnuKumari,

For some reason my responses don't seem to flow through here - not sure if there's an intentional delay or some other reason. But to clarify again (in case, this reaches you :)) - the issue that I'm trying to resolve is that the Parse error happens only with the Mapping Data Flow Request Body - the same JSON works fine when I connect from the Copy Data transform (The reason I can't use it is because the response from the API is a complex JSON with nested Arrays). To your point about escape characters, is there a difference between the way the Copy Data parses & the Data Flow does? Hope that provides context.

Thanks for your response earlier.
AnnuKumari-MSFT 34,566 Reputation points Microsoft Employee Moderator

2023-11-27T09:59:27.9533333+00:00

Hi Gautam Warrier ,

Apologies for delay in response. The way request is treated in copy activity and dataflow might be different. Could you please try to include the request body inside a single quotes in dataflow and let us know how it goes.

Hope it helps. Please accept the answer by clicking on Accept answer button. Thankyou.
Linus Östlund 25 Reputation points

2024-09-24T12:04:40.9333333+00:00

I have the exact same error. I can put my JSON query in Request body, as it gets me the same DSL-parser error. What is the solution? Single quotes did not work.

Share via

How to pass a JSON payload in Azure Data Factory Data Flow? The payload format from Copy Transform throws parse error

1 answer

Your answer