Azure Data factory - Sort values in the Array variable (Append variable activity)

GOVIND RAJ 41 Reputation points
2022-07-25T15:12:23.52+00:00

Hi Team,

I have Append variable activity and written values in the array variable. Is it possible to sort values inside the array variable?

Thanks,
Govind

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
11,246 questions
0 comments No comments
{count} votes

Accepted answer
  1. AnnuKumari-MSFT 34,011 Reputation points Microsoft Employee
    2022-08-02T08:39:04.233+00:00

    Hi @GOVIND RAJ ,

    Thankyou for using Microsoft Q&A platform and thanks for posting your query.

    As I understand your ask, you want to know if there is any way to sort array in ADF . Please let me know if my understanding is incorrect.

    Unfortunately , there is no sort function available that can be utilized in writing expression in ADF. But here is one workaround I have found , you can load the data in SQL table and use order by clause to achieve your desired result.

    1. Use Append variable inside ForEach activity to compose the array .
    2. After append variable , use look up activity inside foreach to load each item as separate record in a table using this query. insert into tablename select @{item()} ; select 1 as output
    3. Use a look up out of the ForEach block, to get the output by using order by clause. select * from tablename order by columnname

    Sharing gif for reference:

    227116-appendvar1.gif

    Hope this will help. Please let us know if any further queries.

    ------------------------------

    • Please don't forget to click on 130616-image.png button and take satisfaction survey whenever the information provided helps you.
      Original posters help the community find answers faster by identifying the correct answer. Here is how
    • Want a reminder to come back and check responses? Here is how to subscribe to a notification
    • If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators

3 additional answers

Sort by: Most helpful
  1. Nandan Hegde 34,171 Reputation points MVP
    2022-07-25T16:18:30.943+00:00
    1 person found this answer helpful.

  2. Nandan Hegde 34,171 Reputation points MVP
    2023-02-25T12:55:42.74+00:00

    Hey @AnnuKumari-MSFT

    The below blog helps to sort an array via regular expressions :

    https://datasharkx.wordpress.com/2023/02/25/sort-an-array-in-azure-data-factory-synapse-pipeline-via-regular-expressions/

    Can you provide your thoughts on that? Or am I missing out anything


  3. Todd Chittenden 21 Reputation points
    2024-01-12T13:47:42.5866667+00:00

    Comment: I find it interesting that the suggestion is to dump the data into, or pass it through, a SQL database. If I had the data already in SQL, the ORDER BY clause would come to mind quite quickly. I also find it quite disappointing that Azure Data Factory (NOT Data Flows) does not have some sort of SORT or ORDER function.

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.