Azure Synapse Analytics, How Use Dynamic Date Function

Question

I'm looking to copy data from S3 to Azure data lake.
Every day the data provider creates a folder that has date of the 5-days-ago in S3.

For instance: sample_data/2022/06/14/Files
Jun 14th, the provider creates the folder of 2022/06/09

Everyday, I have to upload those files automatically using pipeline, but I don't know how use datetime function in Pipeline.

I'm looking to find a way to dynamically write the 2021_08_15 part. I can't figure it out with Microsoft expression language.

I am Japanese so I am sorry for the poor English :(
Any help would be greatly appreciated.

Answer 1

Hey

I got it.

You have to place @markus.bohland@hotmail.de before the bucket name.

Like @markus.bohland@hotmail.de ('bucketname/',formatDateTime(addDays(utcNow(),-5),'yyyy'),'/',formatDateTime(addDays(utcNow(),-5),'MM'),'/',formatDateTime(addDays(utcNow(),-5),'dd'))

Note a slash(/) next to bucket name.
Please try and revert.

Thank you.

Answer 2

Hi,

Thanks for the ask and using MS Q&A portal.

Under the source settings of copy activity, use the below expression, this will make sure to pick up folder with a date that is 5 days ago. Inside the folder whatever file has .txt extension, it will copy all of them to my sink (in my case it was a different container and folder)

@formatDateTime(addDays(utcNow(),-5),'MM-dd-yyyy')

Reference Screenshot with copy settings that worked for me:

Please revert in case you are having questions.

Thank you.