ADF - List months between start/end date parameter and format as yyyymm

Question

Hello,

I need to list folders in a data lake that are named in the YYYYMM format and fall within the pipeline's start and end dates, which are also provided in the YYYYMM format.

Consequently, the pipeline should accept a start/end date parameter, create a list of all months in the YYYYMM format within that range, and then compare this list with the existing folder names in the data lake.

Is this possible to achieve in Azure Data Factory (ADF)?

Thanks!

Answer 1

Hi libpekin ,

Thankyou for using Microsoft Q&A platform and thanks for posting your query here.

I understand your requirement is to fetch the list of months from the provided startDate and endDate in ADF pipeline and compare if the folder is present in the ADLS container or not.

Try the below approach to achive the requirement:

• First of all, you can write a SQL stored procedure to generate the list of months between StartDate and EndDate.

CREATE PROCEDURE sp_GenerateMonths
    @StartDate VARCHAR(6),
    @EndDate VARCHAR(6)
AS
BEGIN
    DECLARE @DateFrom DATE = CAST(@StartDate + '01' AS DATE)
    DECLARE @DateTo DATE = CAST(@EndDate + '01' AS DATE)
    DECLARE @MonthsTable TABLE (MonthYear VARCHAR(6))

    WHILE @DateFrom <= @DateTo
    BEGIN
        INSERT INTO @MonthsTable (MonthYear)
        VALUES (FORMAT(@DateFrom, 'yyyyMM'))

        SET @DateFrom = DATEADD(MONTH, 1, @DateFrom)
    END

    SELECT MonthYear FROM @MonthsTable
END

• Use the Lookup activity in ADF to call the stored procedure, passing StartDate and EndDate as parameters . Lookup activity will give the result array in the output.
• Use getmetadata activity to fetch the list of foldernames in the container using childItems property. This will also give result array as the output.
• Now load both the arrays to SQL table and use join to make the comparison

Hope it helps. Kindly accept the answer by clicking on Accept answer button. Thankyou