Azure Data Factory : returns an array of dates from a specified range

Question

I'm trying to returns an array of dates in data factory. But i just want the user to specify a date range with two parameters, startDate and endDate :

I want to return this array by specifying "12-08-2020" and "12-13-2020" in trigger :

["12-08-2020","12-09-2020","12-10-2020","12-12-2020","12-13-2020"]

Do did not find a simple way to do it yet. One way i thought about would be :

• add a lookup activity on a date dimension,
• then add two filters to select only items greater than startDate and lower than endDate.

But this seems to be cumbersome and overkill. Is there a simpler way to do it ?

---

EDIT :

---

This answer seems to be relevant (i did not see it at first) : Execute azure data factory foreach activity with start date and end date

Answer 1

I think we can use recursive query in Lookup activity.
The pseudo code is as follows:
In sql we can use this query to get a table:

;with temp as 
(
    select CONVERT(varchar(100),'12-08-2020', 110) as dt
union all 
    select CONVERT(varchar(100), DATEADD(day,1,dt), 110) from temp
    where datediff(day,CONVERT(varchar(100), DATEADD(day,1,dt), 110),'12-13-2020')>=0
) select * from temp    

The result is as follows:

So in ADF, I think we can use a Lookup sql query to return the result what you want.

According to this official document, we only need to replace the parameters of the sql statement.

Next,I will use '@{pipeline().parameters.startDate}' to return a date string, note: There is a pair of single quotes outside.

1. I set two parameters as follows:
   

2. Type the following code into a Lookup activity.

;with temp as 
(
    select CONVERT(varchar(100),'@{pipeline().parameters.startDate}', 110) as dt
union all 
    select CONVERT(varchar(100), DATEADD(day,1,dt), 110) from temp
    where datediff(day,CONVERT(varchar(100), DATEADD(day,1,dt), 110),'@{pipeline().parameters.endDate}')>=0
) select * from temp

Don't select First row only.

3. The debug result is as follows: