Dynamic list of variables in process in Azure Data Factory

Question

I have a lookup config table that stores the 1) source table and 2) list of variables to process, for example:

SQL Lookup Table:

tableA, variableX,variableY,variableZ <-- tableA has more than these 3 variables, i.e it has other variables such as variableV, variable W but they do not need to be processed

tableB, variableA,variableB <-- tableB has more than these 2 variables

Hence, I will need to dynamically connect to each table and process the specific variables in each table. The processing step is to convert the julian date (in integer format) to standard date (date format). Example of SQL query:

select dateadd(dd, (variableX - ((variableX/1000) * 1000)) - 1, dateadd(yy, variableX/1000, 0)) FROM [dbo].[tableA]

The problem is after setting up lookup and forEach in ADF, I am unsure how to loop through the variable array (or string, since SQL DB does not allow me to store array results) and convert all these variables into the standard time format.

The return result should be a processed dataset to be exported to a sink.

Hence would like to check what will be the best way to achieve this in ADF?

Thank you!

Answer 1

I have reproed in my local environment. Please see the below steps.

1. Using lookup activity, first get all the tables list from control table.

2. Pass the lookup output to ForEach activity.

3. Inside ForEach activity, add lookup activity to get the variables list from control table where table name is current item from ForEach activity.

     @concat('select table_variables from control_tb where table_name = ''',item().table_name,'''')
   

4. Convert lookup2 activity output value to an array using set variable activity.

@split(activity('Lookup2').output.firstRow.table_variables,',')

5. create another pipeline (pipeline2) with 2 parameters (table name (string) and variables (array)) and add ForEach activity in pipeline2

6. Pass the array parameter to ForEach activity in pipeline2 and Use the copy activity to copy data from source to sink

7. Connect Execute pipeline activity to pipeline 1 inside ForEach activity.