DataFactory Transform ResultSet into a flat array

Question

Hi,
i have a pipeline and a script activity. I use a linked database and and issued a select
The result set is

 "resultSets": [
        {
            "rowCount": 2,
            "rows": [
                { "BranchId": 9133 },
                { "BranchId": 9087 }
            ]
        }
    ]

Now, i like to transform the result set into a flat array e.g. branchIds: [9133,9077] .
How can I do this?

Answer 1

@Jan Schmidt

In addition to @VINODH KUMAR

You can transform the result set into a flat array using a ForEach activity and an Append Variable activity in your Azure Data Factory (ADF) pipeline.

I tried to replicate the scenario and successfully ended up with the expected result.

I took the sample data in SQL database as follows:

I have followed the following steps:

This pipeline executes a SQL query to retrieve BranchId values from the Branches table, iterates over the results, appends them to an array variable, and then stores the final array in another variable.

Run Query to Fetch BranchId - The RunQuery activity executes:

SELECT BranchId FROM Branches;

ForEach Activity to Extract and Append BranchId - The ForEach1 activity processes BranchId values one by one.

Items:

@activity('RunQuery').output.resultSets[0].rows

Inside ForEach - Append Variable activity stores each BranchId inside the BranchIds array.

Store Final Output in Result Variable - After ForEach1 completes, the Set variable1 activity assigns the final BranchIds array to the Result variable.

Expression:

@variables('BranchIds')

Expected Output - After execution, the Result variable should contain:

[9133, 9087, 9055, 9077]

Please refer to the below gif.

I hope this information helps. Please do let us know if you have any further queries.

---

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Answer 2

Hi ,

Thanks for reaching out to Microsoft Q&A.

In ADF=), you can achieve this transformation using an Expression in a Set Variable activity or within a Mapping Data Flow. Here’s how:

Using an Expression in a Set Variable Activity

1. Add a Set Variable activity after your Script Activity.
2. In the Set Variable activity, define a variable (branchIds).
3. Use the following ADF expression to extract and flatten the BranchId values:

@join(activity('YourScriptActivityName').output.resultSets[0].rows[*].BranchId, ',')

• activity('YourScriptActivityName').output.resultSets[0].rows[*].BranchId extracts all BranchId values from the rows.
• join(..., ',') converts it into a comma-separated string.

If you need it as an array instead of a string, store the result in a Parameter instead of a Variable, since ADF variables don’t support arrays.

Using Mapping Data Flow

If you prefer a Data Flow approach:

1. Add a Mapping Data Flow.
2. Use a Source transformation to read from your resultSets.
3. Use a Flatten Transformation to extract BranchId from rows.
4. Use an Aggregate Transformation with collect() to create an array -->collect(BranchId)
5. Pass the transformed array to the next step in your pipeline.

Please feel free to click the 'Upvote' (Thumbs-up) button and 'Accept as Answer'. This helps the community by allowing others with similar queries to easily find the solution.