Search code examples
arraysazure-data-factoryexpression

Creating Array of Arrays in Azure Data Factory

I have a requirement to create a date array in Azure Data Factory as below. The start and End Date will be provided and the resultant array of array should look like the below

[["01-Jan-2022","31-Mar-2022"],["01-Apr-2022","30-Jun-2022"]]

the interval between the dates need to be 3 months each.

I am providing a start date and an end date in the parameters 'StartDate' and 'EndDate'. I have tried doing this using a For Each Loop, but couldn't get it through.

I need to loop through this array of dates and create a SOAP request with each set of start and end dates.

Solution

    • Create variables in pipeline like in below screenshot.

    • Take a until activity and give the below expression

    @greater(addDays(addToTime(variables('new_date'),6,'Month'),-1),pipeline().parameters.EndDate)

    Until activity runs till this expression evaluates to false. Inside until activity, take four set variable activities.

    • Store the value of variable new_date1 and arr_str in new_date and temp respectively using set variable activities.

    • Take another set variable activity and store the value for arr_str using below expression

    @concat(variables('temp'),',',string(createArray(formatDateTime(variables('new_date'),'dd-MMM-yyyy'),formatDateTime(addDays(addToTime(variables('new_date'),3,'Month'),-1),'dd-MMM-yyyy'))))

    • Take another set variable activity and store the value for new_date1 using below expression @addToTime(variables('new_date'),3,'Month').

    • Take set variable activity outside the loop and connect it sequentially with until activity. Expression for arr_out is given as below.

    @json(concat('[',substring(variables('arr_str'),1,add(length(variables('arr_str')),-1)),']'))

    The arr_out array will give the required output between start date and end date.