Every since I’ve started using Synapse for an assignment, I’ve preferred using Spark Notebooks to get anything done. However, they take time to spin up, something I wanted to mitigate by using Synapse-native components. In this article, we’ll generate a date range without Spark notebooks.
It’s unnecessarily complicated, but you can definitely create a data range using Synapse/ADF activities only. Let’s dive right in.
First, you should create pipeline parameters which mark the start and the end of the date range. Both should be string.
![](https://www.roelpeters.be/wp-content/uploads/2022/11/image.png)
You should also set the pipeline variables. The date_range variable is an array will contain the constructed date range. The last_date_intermediate will constantly update with the next date in the until loop we’ll set later.
![](https://www.roelpeters.be/wp-content/uploads/2022/11/image-5.png)
Next, create an until activity and provide it the following expression. This means it will keep looping until the last item of the array variable date_range is the same as the end parameter.
@equals(last(variables('date_range')),pipeline().parameters.end)
Within the until activity, we’ll create two sequential activities: a set variable activity and an append variable activity.
![](https://www.roelpeters.be/wp-content/uploads/2022/11/image-6.png)
While the until loop runs, the first activity, the set variable activity, will set the last_date_intermediate variable to the next date between the start and end date specified in the pipeline parameters.
@if( equals(length(variables('date_range')),0), pipeline().parameters.start, substring( addDays( concat(last(variables('date_range')),'T01:00:00Z'), 1 ), 0, 10 ) )
This is what it looks like in the GUI:
![](https://www.roelpeters.be/wp-content/uploads/2022/11/image-3.png)
The append variable activity should be parametrized with the following dynamic content. It will read the newly set value from the last_date_intermediate variable, and append it to the date_range array variable.
@variables('last_date_intermediate')
This is what it looks like in the GUI:
![](https://www.roelpeters.be/wp-content/uploads/2022/11/image-4.png)
If you though you could eliminate the first set variable activity by doing the calculation on the last date from the date_range array, inside the append variable activity, think again. You’ll run into this error:
The expression contains self referencing variable. A variable cannot reference itself in the expression.
Your until activity is is basically done now. If you’d like to loop over the constructed array, you should use a ForEach activity. Its items should refer to the date range array variables.
@variables('date_range')
In the GUI:
![](https://www.roelpeters.be/wp-content/uploads/2022/11/image-7.png)
Finally, inside the ForEach activity, you can refer to the individual dates of the date range, simply by referring to the @item().
![](https://www.roelpeters.be/wp-content/uploads/2022/11/image-2.png)
The final result should look something like this. I’ve used a subpipeline in the ForEach activity, but choose whatever floats your boat.
![](https://www.roelpeters.be/wp-content/uploads/2022/11/image-1.png)