This post will describe how to add your local timestamp at the end of the each file in Azure Data Factory (ADF). In general, ADF gets UTC timestamp so we need to convert the timestamp from UTC to EST, since our local time zone is EST.
For example: Input Source file name in SFTP is: _source_Customer.csv and the expected outcome will be: _source_Customer_2021-02-12T133751.csv
It means, the pipeline should add '_2021-02-12T133751' end of each file. And this will work dynamically means any file you pass from source will add it by using ADF regular expression.
Let's set a simple pipeline and explain the scenario in a few steps. In this example, we receive files from event based trigger and hold the file in a parameter, main learning of this article is how to pad current date time end of the file name which we received from the event base trigger. Please note that, event based trigger will not be discussed here, If you like to know more about how to create trigger please follow the link.
Step 1: Add Copy Activity
Create a simple pipeline where need to have at least one Copy activity which connect source and sink as like below figure 1.0
Step 2: Adding a parameter to receive file name from trigger
Step 3: Prepare sink dataset
In Copy data activity you got Sink dataset, you need to add a parameter in the dataset, Click on Sink dataset and Open it then you will find as like below diagram 1.2
Now, let's go the the dynamic expression. As soon you hit the 'Add dynamic content' like below figure 1.4 you will able to write the expression which will convert the UTC timestamp to EST and then pad end of the file.
Let's have closer look into the expression:
@concat(replace(pipeline().parameters.pTriggerFile,'.csv',''), '_', formatDateTime(convertTimeZone(utcnow(),'UTC','Eastern Standard Time'),'yyyy-MM-ddTHHmmss'), '.csv')
The functions which have been used to accomplish the task: @concat @replace @convertTimeZone and @formatDateTime
Explanation of the above expression?
1 : First we need to get the filename from parameter :_source_Customer.csv
2: To replace the .csv with empty string : replace(pipeline().parameters.pTriggerFile,'.csv','') which will return: _source_Customer
3: Convert utcnow() to EST : convertTimeZone(utcnow(),'UTC','Eastern Standard Time')
4: want to format the date: formatDateTime(convertTimeZone(utcnow(),'UTC','Eastern Standard Time'),'yyyy-MM-ddTHHmmss') which will return 2021-02-12T133751
5: Concat from earlier steps and add .csv at the end @concat(Step1,'_', 'Step4','.csv') which will return _source_Customer_2021-02-12T133751.csv
We learned how to add local timestamp end of any file, in this sample source file was .csv. However, you can follow the same process for .txt file where you only need to change '.csv' to '.txt' in the expression.
No comments:
Post a Comment