Copy latest files from SAP Server using Azure Data Factory and File System Linked Service and store it in Azure Data Lake

Question

I have a task to copy the newly added files everyday from SAP Server and store it in ADLS. There are 2 types files on the server (Recurring and Upfront) appended with date. I need to store the files in separate folder and everyday add the latest file from the SAP Server to the ADLS.

File name format: R_07292021.orc Recurring_08312021.orc U_07292021.orc Upfront_08312021.orc

Below are the steps I have taken so far

1. Get Metadata Activity to get the list of files from the server

2. Use filter activity to separate the files based on the names, so filtering with the initial letter

3. I tried using the Foreach activity and If Condition, but it doesn't seem to be working.

I am stuck at this point trying to figure out how to proceed. Any help would be very much appreciated.

Answer 1

If you are trying to get the latest modified date of a file from a folder, you can refer to the below process.

I tested it with one type of file which starts with “U”.

1. Create 2 variables, one to store maxdate and the other to store the latestfilename. Assign any initial value (which is the past date) in maxdate variable.

2. Using Get Metadata activity getting the list of files that starts with “U” by hardcoding the filename parameter value as “U”.

Output of Get Metadata1:

3. Pass the Get Metadata activity output child items to the ForEach activity to loop through all the files from the list.

4. Inside ForEach--> Use another Get Metadata activity to get the metadata (last modified date & filename) of the current file in the loop.

Output of Get Metadata2:

• Connect Get Metadata to If Condition and use greater function and ticks function to evaluate the If condition expression.

• Ticks function returns Integer value of specified timestamp and using greater function compare the 2 values.

    @greater(ticks(activity('Get_lastmodified_date_and_name').output.lastModified),ticks(formatDateTime(variables('maxdate'))))
  

6. When the expression evaluates to true add (2 set variable) activities to store the last modified date and respective file name into the variables created initially.

Maxdate:

LatestFileName:

Note: These variable's values will be overridden if the next iteration file in the loop contains a timestamp greater than the previous (or first) loop file.

7. In the next activities, Use the variable filename (latestfilename) to assign the value for the filename parameter in the source.