ADHOC Pipeline execution based on date range(startdate and endate)

pankaj chaturvedi 86 Reputation points
2020-11-18T15:05:47.357+00:00

Hi Team,

I want to execute the pipeline based on start date and enddate and it should load the data into ADLS in the incremental way. for example start date=2020-11-14 and enddate=2020-11-17 then it should load the file into data lake like filename_2020-11-14.csv,filename_2020-11-15.csv,filename_2020-11-16.csv

i am using currently

start date and enddate are using as parameters
incdate,date are using as variables

setvariable activity(@pipeline().parameters.startdate)-->until activity(@greater(formatDateTime(variables('incdate'),'yyyyMMdd'),formatDateTime(pipeline().parameters.enddate,'yyyyMMdd')))-->copyactivity---->setvariable (date) activity (@pipeline().parameters.startdate)---->set variable(incdate) activity(@adddays(variables('date'),1))

it is not incrementing the startdate,everytime is loading the data for 2020-11-14.
Could you please help me here if anyone has idea.

I am looking forward your response.

Thanks,
Pankaj

Azure Data Lake Storage
Azure Data Lake Storage
An Azure service that provides an enterprise-wide hyper-scale repository for big data analytic workloads and is integrated with Azure Blob Storage.
1,466 questions
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
10,681 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Saurabh Sharma 23,816 Reputation points Microsoft Employee
    2020-11-20T01:15:41.54+00:00

    @pankaj chaturvedi Are you passing required date range to your REST API in the copy activity ? You need to pass dates to your REST API so that you can get the data between the start date and end date range from REST API. I have not tested it yet with REST API but with SQL source able to achieve it -

    • Set Date variable then in copy activity passing the same Date to form a dynamic query to get data of that particular date only in Source.
      41224-image.png
    • Now, in the Sink Dataset I am using the same Date variable to concatenate and create the FileName as shown below.
      41138-image.png
      Here is the Sink dataset is configured to take the FileName as parameter to create the respective file with data.
      41188-image.png
      41273-image.png

    Output:
    ![41196-image.png][5]

    0 comments
  2. Saurabh Sharma 23,816 Reputation points Microsoft Employee
    2020-11-20T01:20:19.34+00:00

    Here is the output -
    41233-image.png
    41209-image.png

    My SQL table has one records for each day so the files are getting generated with only one record.
    Also, if your REST API is capable of providing dates between the range passed through Copy Activity then you would be able to achieve the same using similar approach.
    Please let me know if this works for you.

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.