How to get the file properties from Azure Storage using ADF/Synapse Pipeline for a date range?

In this blog we are going to see how to get the file which was dumped to the blob storage yesterday.
This was one of the requirements that people asked me to get the file name that was pushed to the storage yesterday(or in any date range). I have the files stored in ADLS Gen2.
Here I am going to demonstrate on how to take the filename using the ADF/Synapse Pipeline from the storage and store the file properties in SQL Table.

We are going to use the three activities in the Pipeline for the design.

Get Metadata
For Each activity
SQL Stored Procedure

Get Metadata Dataset:
we are going to use the Get Metadata Activity to get all the files stored under specified folder.
we have to select the file which was created since yesterday by giving the range dynamically.
The list of the file can be retrieved using the Field name childitems.
I am using the Binary dataset. you can choose according to your field requirements.
First MetaData activity will take the File path as an input and will give the list of file that is under the folder.

Let’s design the Get Metadata activity:

We will get the file name under the folder.
You can use the another Get Metadata activity to find the filename, it’s Last Modified Date, Size etc.

There could be scenarios where there might be multiple files was created since yesterday then you need to use the For Each Activity to know each file’s property one by one.

you need to select the checkbox for sequential to process each file one by one.
Inside the ForEach we will place the another Get MetaData activity to grab the file properties.

Here we have passed the filename in the dataset one by one. The path name needs to be entered manually.

you can see in the above screenshot there is an option for Parameters, you need to click on it and create a parameter Filename. The Filename parameter will be passed as dynamic content in the second GetMetadata activity inside the ForEach.

Let’s create a parameterized Stored PROC and Table that would save all the filename and their properties.

Table schema:

CREATE TABLE DBO.FILE_PROPERTY
(
FILENAME VARCHAR(30) NULL,
LASTMODIFIED VARCHAR(30) NULL,
FILESIZE VARCHAR(50) NULL
)

Stored Procedure:

CREATE PROC [dbo].[File_PROPERTIES] @filename varchar,@Lastmodified varchar, @FileSize varchar AS
BEGIN

insert into Dbo.FILE_PROPERTY
([filename]
,[Lastmodified]
,FileSize
) SELECT @filename,@Lastmodified, @FileSize
end;

Let’s configure the Stored Procedure activity.

You need to open the value field of each column you imported from the Stored procedure and click on the Add dynamic Content and pass the output from previous Get Metadata one by one.