Add Azure Blob Partitions to Azure SQL Table

Question

I have partitioned parquet files in Azure Blob that I am copying to Azure SQL. How do I get the partition name into the SQL table?

I've figured out how to get the full file path into the SQL table by adding an Additional Column in the source data section of the Copy Activity (image 1 & 2), but I'm trying to figure out how to regex the full file path down to just the partition name (202105).

In the data preview for the source data in the Copy Activity, it shows the time_period column with just the partition name (image 3). But when it shows up in SQL it is NULL for all rows (or it's the full file path, depending on if I added Additional Columns in the source data section of the Copy Activity).

Image 1:

Image 2:

Image 3:

I've tried changing the data type for time_period to an INT in Azure SQL. I've tried parsing the $$FILEPATH, but nothing I've tried has worked.

I'm basically starting from scratch as I'm sure there's a better.
Extra background here.

Similar to this