I would like to get the data for the previous 2 weeks from the date specified in the parameter, and I would like to know how to get the data for 2 weeks in one pipeline. The data is stored in GEN2 as AAA/year/mm/day by day. Do I use variables or for each or until activities? Sorry for my lack of study, but the image is like this.

Hi @ ponponpon , Welcome to Microsoft Q&A forum and thanks for reaching out here. As per my understanding, you have an input parameter, which carries a date value and based on that you will have to copy the last 2 weeks data from the provided date to your desired sink data store and your source is ADLS Gen2 with folder structure as Please correct if my understanding is not accurate. To achieve your requirement, rather getting each date and looping through each folder, the ideal solution would be to utilize the wildcard file path feature in combination with Filter by last modified feature in copy activity to copy only the file data for past 2 weeks from the given date using a single copy activity. Please follow below settings in your Copy activity. Since you are having parameter for date as reference point. You can use adddays() function to find the past 14 days date as @adddays(pipeline().parameters.param_inputDate, -14) which you will pass as Start time for filter by last modified property and for End time you can pass your input date parameter as is ( @pipeline().parameters.param_inputDate ) and then select Recursively property to true so that all files under Kranthi/AAA which have datetime in the past 2 weeks will get copied to your sink. Please note that above solution best suits when your dated folder contains files with created/modified datetime as the folder name date. Incase if your file folder name date and file created/modified datetime varies, please let me know. Hope this info helps. Let us know if you have further questions. Please don’t forget to Accept Answer and Yes for "was this answer helpful" wherever the information provided helps you, this can be beneficial to other community members.

Please tell me how to retrieve data.

Accepted answer

KranthiPakala-MSFT 46,442 Reputation points Microsoft Employee

2023-02-28T20:47:22.94+00:00

Hi @ ponponpon ,

Welcome to Microsoft Q&A forum and thanks for reaching out here.

As per my understanding, you have an input parameter, which carries a date value and based on that you will have to copy the last 2 weeks data from the provided date to your desired sink data store and your source is ADLS Gen2 with folder structure as

Please correct if my understanding is not accurate.
To achieve your requirement, rather getting each date and looping through each folder, the ideal solution would be to utilize the wildcard file path feature in combination with Filter by last modified feature in copy activity to copy only the file data for past 2 weeks from the given date using a single copy activity.

Please follow below settings in your Copy activity.
Since you are having parameter for date as reference point. You can use adddays() function to find the past 14 days date as @adddays(pipeline().parameters.param_inputDate, -14) which you will pass as Start time for filter by last modified property and for End time you can pass your input date parameter as is (@pipeline().parameters.param_inputDate) and then select Recursively property to true so that all files under Kranthi/AAA which have datetime in the past 2 weeks will get copied to your sink.

Please note that above solution best suits when your dated folder contains files with created/modified datetime as the folder name date. Incase if your file folder name date and file created/modified datetime varies, please let me know.

Hope this info helps. Let us know if you have further questions.

Please don’t forget to Accept Answer and Yes for "was this answer helpful" wherever the information provided helps you, this can be beneficial to other community members.
Please sign in to rate this answer.
NishimuraChinatsu-9854 756 Reputation points

2023-03-02T07:03:47.36+00:00

Thank you for your response. I think you are correct in your understanding of what I want to do. I tried with pipeline and the pipeline runs successfully, but I wasn't getting any data. I have tried several things, but it does not work. The parameters are as follows prm_date→yyyy-mm-dd prm_directory→ @concat('src_cake/delta/sales_detail/year=',formatDateTime(addDays(utcNow(),-1),'yyyy'),'/','month=',formatDateTime(addDays(utcNow (),-1),'MM'),'/','day=',formatDateTime(addDays(utcNow(),-1),'dd')) prm_file→ABC.parquet start time→@adddays(pipeline().parameters.prm_date,-15) end time→@adddays(pipeline().parameters.prm_date,-1) By the way, there are two files in dd. Only one particular one of them needs the same file. Example:) AAA/year=yyyy/month=mm/day=dd/AA.parquet BB.parquet Can you please help me what is wrong?

KranthiPakala-MSFT 46,442 Reputation points Microsoft Employee

2023-03-03T23:47:56.96+00:00

@NishimuraChinatsu-9854 To better assist on this, could you please share a screenshot of your copy activity settings and your dataset settings.

At an initial glance, I suspect there is an issue in wild card file path, you will have to just provide the root folder and have a wild card notation as * and in the file name as fileName*.parquet (please follow screenshot in my previous response)

NishimuraChinatsu-9854 756 Reputation points

2023-03-06T05:01:54.1533333+00:00

prm_date→yyyy-mm-dd

prm_directory→

@concat('src_cake/delta/sales_detail/year=',formatDateTime(addDays(utcNow(),-1),'yyyy'),'/','month=',formatDateTime(addDays(utcNow (),-1),'MM'),'/','day=',formatDateTime(addDays(utcNow(),-1),'dd'))

prm_file→AA*.parquet

start time→@adddays(pipeline().parameters.prm_date,-15)

end time→@adddays(pipeline().parameters.prm_date,-1)

By the way, there are two files in dd.

Only one particular one of them needs the same file.

Example:)

AAA/year=yyyy/month=mm/day=dd/AA.parquet

I ran it with * in the wildcard path, but I can't get any data.

The image shows the copy activity and data set.

Thank you very much.

KranthiPakala-MSFT 46,442 Reputation points Microsoft Employee

2023-03-08T23:10:23.2266667+00:00

@NishimuraChinatsu-9854 - Thanks for additional details.

After looking at your source Wild card path settings, I suspect that you concatenation is creating the problem. And also noticed that you have also parameterized your dataset which is not needed in this requirement as you are utlizing the wild card file paths in source settings and filterBylastModified option to only copy last 2 weeks file data.

To make it simple and avoid confusions, I would recommend using a simple dataset (without dataset parametization) and then use wildcard paths settings as below without dynamics expression. Since we are utilizing wildcard paths here you may use regex like * so that It will go through the folders for the past 2weeks and pull the data. Please follow configuration as below (pleas do not use dynamic expressions here)

In your dynamic expression you shared above - it resolves to a single day and not last 2 weeks.

Hope this clarifies. Please let me know if you still have any questions.

Thank you

KranthiPakala-MSFT 46,442 Reputation points Microsoft Employee

2023-03-11T16:43:54.0966667+00:00

@NishimuraChinatsu-9854 following up to see how it went with above implementation. Let me know if you have any questions.

Thank you

NishimuraChinatsu-9854 756 Reputation points

2023-03-14T05:18:28.1566667+00:00

We were able to retrieve the data.

However, the data is not taken as specified.

When I look at the input, the date data is correct for what I want to retrieve. When I actually run the pipeline, I get all the data for the month of February.

Thank you again for your help.

KranthiPakala-MSFT 46,442 Reputation points Microsoft Employee

2023-03-15T23:43:22.69+00:00

@NishimuraChinatsu-9854 - Glad to know you were able to retrive the data. And regarding the start date problem you called out, you will have to play around with adddays() function and datetime functions to get the appropriate dates data as needed.

Hope this info helps. Please let me know if you have any futher questions.

Please don’t forget to Accept Answer and Yes for "was this answer helpful" wherever the information provided helps you, this can be beneficial to other community members.

NishimuraChinatsu-9854 756 Reputation points

2023-03-16T10:40:27.8233333+00:00

I apologize for the lack of words in my explanation.

As for the date of data retrieval, the input shows February 27 to March 13 as shown in the image, but the stored data was from February 1 to February 28.

I would like to know the cause of this.

Do you mean that this cause is addday()?

KranthiPakala-MSFT 46,442 Reputation points Microsoft Employee

2023-03-16T15:30:10.29+00:00

@NishimuraChinatsu-9854 - Yes, you are correct, the Start Time value will be auto generated based on the dynamic expression you have for Start tIme in Fiter by last modified property. Please configure according to your need. I believe your original requirement is look for last 14 calendar days and pull the files.
And also please note that the filter times are by default UTC format, if you are looking for different timezone format then you will have to use date conversion functions to make up your desired timezone date time.

NishimuraChinatsu-9854 756 Reputation points

2023-03-22T09:48:27.2866667+00:00

Thank you very much. I will think about it.
Sign in to comment

Share via

Please tell me how to retrieve data.

0 additional answers