We wish to ingest data from an on-prem SQL DB into ADLS Gen 2 using ADF. The desired folder structure will be - <Object> YYYY MM DD <file><datetime> How do I go about creating a pipeline that will load historical data into the ADLS folder structure above. I am clear on how to do this for an ingestion point forward, but would like guidance for historical load. I would go with either create date/update date of record from source table for organizing the files in the ADLS.

Hi @Kia , Welcome to Microsoft Q&A Platform. Thanks for posting the query. I have implemented it till year and month level and same can be extended to dates in the data. I have used couple of stored procedures in on-premise SQL as below dbo.getmonths with input year parameter to retrieve months for the specific year from the table dbo.getdata with input year and month parameters to retrieve data for specific month and year from the table Design includes following components Lookup - to retrieve list of years and months from the table, this can be added for days as well Foreach activity - to loop through years/months based on lookup output Execute Pipeline - Since foreach inside foreach is not supported in azure data factory, implemented the copy activity in child pipeline with input parameter as year. If extended to days, the input parameters can be both year and month Copy Activity - This activity is used to load the data from the SQL to blob with dynamic folders using month and year Below GIF shares the details on implementation. Also, attached the json of pipelines to this. 31625-parentpipelinejson.txt 31626-childpipelinejson.txt Hope this helps! Please let us know if it does not align with the requirement or for further queries and we will be glad to assist.

hi, Daily I am getting N no.of files with the file name as 'ABC_YYYYMMDD.csv' format. How can I create the Year & Month folders dynamically in adls gen2 and how can I load the files based on the year and month? Ex: All the files coming in Jan should be loaded in Jan folder, Feb files should be loaded in Feb folder.. etc

Azure Data Factory - Create Dynamic Destination Folders for Historical Load

Accepted answer

HarithaMaddi-MSFT 10,136 Reputation points

2020-10-12T09:21:50.677+00:00
Hi @Kia ,

Welcome to Microsoft Q&A Platform. Thanks for posting the query.

I have implemented it till year and month level and same can be extended to dates in the data. I have used couple of stored procedures in on-premise SQL as below

dbo.getmonths with input year parameter to retrieve months for the specific year from the table

dbo.getdata with input year and month parameters to retrieve data for specific month and year from the table

Design includes following components

Lookup - to retrieve list of years and months from the table, this can be added for days as well

Foreach activity - to loop through years/months based on lookup output

Execute Pipeline - Since foreach inside foreach is not supported in azure data factory, implemented the copy activity in child pipeline with input parameter as year. If extended to days, the input parameters can be both year and month

Copy Activity - This activity is used to load the data from the SQL to blob with dynamic folders using month and year

Below GIF shares the details on implementation. Also, attached the json of pipelines to this.
31625-parentpipelinejson.txt
31626-childpipelinejson.txt

Hope this helps! Please let us know if it does not align with the requirement or for further queries and we will be glad to assist.
Please sign in to rate this answer.

1 person found this answer helpful.
Kia 31 Reputation points

2020-10-13T00:33:25.793+00:00

Thank You for taking the time and effort to reply.
Definitely, the response provided helps me to a great deal.

Could you please elaborate on how would I go about creating a file in the blob with datetime = create date of the record.

For example in the source we have -
PersonId Name DoB CreateDate
P0001 John 01/01/1953 01/01/2016

How would I create file - <id>_<createDate> i.e. P0001_01012016.txt in the blob?
Can this be achieved purely using ADF v2?

Also, I was reading other blogs as well and had another question based on my research which is - could a tumbling window trigger be a solution as well in this scenario and make it a simpler solution?

Once again, thank you for your response.

HarithaMaddi-MSFT 10,136 Reputation points

2020-10-13T11:32:26.913+00:00

Hi @Kia ,

Thanks for sharing additional details.

Below GIF shows the above approach extended to day folder as well and also file name will be coming up as ID_createdate.txt as shown. Attaching the json files of pipelines as well. Tumbling window triggers will trigger the pipeline in periodic intervals and keep track of the last run date time. As per my understanding, in this scenario, since we want to direct file to appropriate folder in the blob, it is necessary for pipeline to read every record based on its date time and to redirect, it is not suitable for this.

32022-parentpipelinejson.txt
32023-monthchildpipelinejson.txt
32024-daychildpipelinejson.txt

Kia 31 Reputation points

2020-10-19T23:37:36.397+00:00

Hello @HarithaMaddi-MSFT

What are my alternatives for creating stored procedures in on-premise SQL?

HarithaMaddi-MSFT 10,136 Reputation points

2020-10-20T07:15:04.927+00:00

Thanks @Kia for posting the question. Currently in Azure Data Factory, we have no ability to run individual SQL scripts to retrieve the values and hence we used Stored Procedures to achieve the same.

There is a idea in feedback forum that is under review related to this requirement. I would recommend to upvote this idea as these are closely monitored by Data Factory product team and will be considered in future releases.

Please let us know for further queries and we will be glad to assist.

HarithaMaddi-MSFT 10,136 Reputation points

2020-10-20T07:15:55.127+00:00

Thanks @Kia for posting the question. Currently in Azure Data Factory, we have no ability to run individual SQL scripts to retrieve the values and hence we used Stored Procedures to achieve the same.

There is a idea in feedback forum that is under review related to this requirement. I would recommend to upvote this idea as these are closely monitored by Data Factory product team and will be considered in future releases.

Please let us know for further queries and we will be glad to assist.

Kia 31 Reputation points

2020-10-20T15:37:44.617+00:00

No option of reading from files right right? Even if it means creating a static list of year, month and day. I ask cuz I don't have the ability to create any SPs in my source which is salesforce.

HarithaMaddi-MSFT 10,136 Reputation points

2020-10-21T09:54:18.153+00:00

Thanks @Kia for posting the query. As mentioned in the question earlier, source is on-premise SQL and the solution approach aligns with that. Please suggest if source is different other than SQL.

Kia 31 Reputation points

2020-10-21T12:32:11.323+00:00

Apologies, yes the additional requirement is to obtain data from salesforce as well. Your response aligns with SQL. Would you like me to post a different question for salesforce?

HarithaMaddi-MSFT 10,136 Reputation points

2020-10-22T06:04:48.767+00:00

Thanks @Kia for sharing details. Unfortunately it can be only using static list of year, month and day if it is salesforce source. Instead of lookup activities to get list of years, months, days we can define array parameters with static list and rest of the design can remain same.

Please suggest for further queries.

Kia 31 Reputation points

2020-10-22T12:45:45.447+00:00

Was thinking on similar lines.. thank you
Sign in to comment

Share via

Azure Data Factory - Create Dynamic Destination Folders for Historical Load

1 additional answer