I am trying to parse the $$FILEPATH value in the "Additional columns" section of the Copy Activity.

The filepaths have a format of: time_period=202105/part-12345.parquet . I would like just the "202105" portion of the filepath. I cannot hardcode it because there are other time_period folders.

I've tried

Get Metadata Output:

2. Pass the output to the ForEach activity to loop all the folders.

3. Add a variable at the pipeline level to store the folder name.

4. In the ForEach activity, add the set variable activity to extract the date part from the folder name and add the value to the variable.

   @substring(item().name, add(indexof(item().name, '='),1), sub(length(item().name), add(indexof(item().name, '='),1)))
   

Output of Set variable:

5. In source dataset, parameterize the path/filename to pass them dynamically.

6. Add copy data activity after set variable and select the source dataset.

a) Pass the current item name of the ForEach activity as a file path. Here I hardcoded the filename as *.parquet to copy all files from that path (this works only when all files have the same structure).

b) Under Additional Column, add a new column, give a name to a new column, and under value, select to Add dynamic content and add the existing variable.

7. Add Sink dataset in Copy data Sink. I have added the Azure SQL table as my sink.

8. In Mapping, add filename (new column) to the mappings.

9. When you run the pipeline, the ForEach activity runs the number of items in the Get Metadata activity.

Output: