Home > Blockchain >  How to transform a column into an array using ADF
How to transform a column into an array using ADF

Time:01-31

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

CodePudding user response:

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

CodePudding user response:

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

  • Related