Question 103692 in Azure Question posted in
The official documentation to get you started can be found here.

Azure – How to transform a column into an array using ADF

I need to read a column on a db on ADF and use all it´s values as parameters in a foreach.

I tried reading the column using a dataflow and a cache sink to then in a pipeline use Set Variable and then the foreach…but instead of an array of values I get an array with one value that contains all the others I want (but i cant iterate over)
I am using:
@array(activity('myDataflow').output.runStatus.output.columName

Any help is appreciated, seems simple enough (column to array) but I am stuck

Tags:

2

Answers


  1. 0

    Use a lookup activity to get the data from SQL server, and run for each loop on the output of the lookup.
    Example:

    1. Create a new pipeline

    2. Add a lookup activity

    3. Choose your source dataset (in this example, an Azure SQL database)

    4. Remove the checkbox from “First row only”

    5. Choose a table, stored procedure or type in a query

      SELECT 1 AS result UNION ALL
      SELECT 2 AS result UNION ALL
      SELECT 3 AS result UNION ALL
      SELECT 4 AS result
      lookup activity

    6. Add a foreach activity

    7. In the foreach activity, under settings tab: “Items” –
      @activity(‘Lookup SQL query’).output.value – where ‘Lookup SQL
      query’ is the name of the lookup activity
      for each activity

    8. Inside the foreach loop, add a wait activity

    9. In the settings tab, “Wait time in seconds” : @item().result .
      item() is the current loop, and result is the name of the SQL column
      wait activity

    debug the pipeline. You can see that the foreach activity iterates 4 times, for every row returned from the sql query.
    lookup activity results

    Login or Signup to reply.
  2. 0

    You can use append variable activity also, inside ForEach after lookup.

    First create an array variable in the pipeline.

    enter image description here

    Then use append variable activity inside ForEach and give

    @item.<your_column_name>

    enter image description here

    Result variable stored in a sample variable:

    enter image description here

    Result:

    enter image description here

    I tried reading the column using a dataflow and a cache sink to then in a pipeline use Set Variable and then the foreach

    If you want to do it with dataflows instead of lookup, use the same above procedure and give the below dynamic content in the ForEach.

    @activity('Data flow1').output.runStatus.output.sink1.value

    enter image description here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search