Azure Synapse Pipeline Bad Request when Adding Set Variable Activity

I have a simple Synapse pipeline that queries a SQL database. The query works as expected, but when I try to add a Set Variable activity to output the result, I get the following error:

{ "code": "BadRequest",
  "message": null,
  "target": "pipeline//runid/<runID goes here>",
  "details": null,
  "error": null
}

My synapse pipeline only has two activities the script and the set variable:

{
"name": "query_resource",
"properties": {
  "activities": [
      {
          "name": "Test query",
          "type": "Script",
          "dependsOn": [],
          "policy": {
              "timeout": "0.12:00:00",
              "retry": 0,
              "retryIntervalInSeconds": 30,
              "secureOutput": false,
              "secureInput": false
          },
          "linkedServiceName": {
              "referenceName": "***************",
              "type": "LinkedServiceReference"
          },
          "typeProperties": {
              "scripts": [
                  {
                      "type": "Query",
                      "text": {
                          "value": "@pipeline().parameters.query",
                          "type": "Expression"
                      }
                  }
              ],
              "scriptBlockExecutionTimeout": "02:00:00"
          }
      },
      {
          "name": "Query Result",
          "type": "SetVariable",
          "dependsOn": [
              {
                  "activity": "Test query",
                  "dependencyConditions": [
                      "Succeeded"
                  ]
              }
          ],
          "policy": {
              "secureOutput": false,
              "secureInput": false
          },
          "userProperties": [],
          "typeProperties": {
              "variableName": "pipelineReturnValue",
              "value": [
                  {
                      "key": "queryResult",
                      "value": {
                          "type": "Object",
                          "content": "@activity('Test query').output.resultSets"
                      }
                  }
              ],
              "setSystemVariable": true
          }
      }
  ],
  "parameters": {
      "query": {
          "type": "string",
          "defaultValue": "SELECT * FROM OPENQUERY(*********, 'SELECT Some query that is successful')"
      }
  },
  "annotations": [],
  "lastPublishTime": "2024-10-25T22:05:38Z"
},
"type": "Microsoft.Synapse/workspaces/pipelines"
}

I expect the pipeline to run successfully and the result of the query to be stored in the variable.

Solution

I got the same ERROR like you when using the Set Variable Type as String & Set variable Value as @activity('Script1').output.resultSets:

enter image description here

Here is the correct Pipeline JSON which worked for me using the Variable created as String:

enter image description here

{
    "name": "Pipeline 1",
    "properties": {
        "activities": [
            {
                "name": "Script1",
                "type": "Script",
                "dependsOn": [],
                "policy": {
                    "timeout": "12:00:00",
                    "retry": 0,
                    "retryIntervalInSeconds": 30,
                    "secureOutput": false,
                    "secureInput": false
                },
                "userProperties": [],
                "linkedServiceName": {
                    "referenceName": "AzureSqlDatabase1",
                    "type": "LinkedServiceReference"
                },
                "typeProperties": {
                    "scripts": [
                        {
                            "type": "Query",
                            "text": "SELECT TOP 1 FileName FROM filestatus\n"
                        }
                    ],
                    "scriptBlockExecutionTimeout": "02:00:00"
                }
            },
            {
                "name": "Set variable1",
                "type": "SetVariable",
                "dependsOn": [
                    {
                        "activity": "Script1",
                        "dependencyConditions": [
                            "Succeeded"
                        ]
                    }
                ],
                "policy": {
                    "secureOutput": false,
                    "secureInput": false
                },
                "userProperties": [],
                "typeProperties": {
                    "variableName": "QueryResult",
                    "value": {
                        "value": "@activity('Script1').output.resultSets[0].rows[0].FileName",
                        "type": "Expression"
                    }
                }
            }
        ],
        "variables": {
            "QueryResult": {
                "type": "String"
            }
        },
        "annotations": []
    }
}

enter image description here

In the above pipeline I have used value for the Set Variable activity for String Type like below: @activity('Script1').output.resultSets[0].rows[0].FileName

Results:

enter image description here

2nd approach: You can use the Set Variable Type to ARRAY

enter image description here

And then use the Value for the Set Variable activity like below:

@activity('Script1').output.resultSets

Results: enter image description here