How to iterare all the excel sheets present in a excel file in azure data factory

Question

I have an Excel file with 5 sheets: Sheet1, Sheet2, Sheet3, Sheet4, Sheet 5. In the future, the user can add Sheet6, Sheet7 as well.

I want to create a pipeline to copy all the sheet data into a single table. I want to iterate all the sheets in excel and copy the data from Sheet to a single table.

As per my approach, I have created an Array variable and assigned ["Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5"] and I am using a Foreach loop and Inside Foreach, I am copying the Sheet data to a single table.

In my second approach, I am using a Lookup activity to fetch the sheet info from a SQL table and then using foreach loop to copy the sheet's data into the table.

But, in both the approach, whenever a user adds a new sheet, either, I need to update my ADF pipeline (approach 1) or I need to update my SQL table where Sheet info is present.

I don't want to update either the pipeline or SQL table to fetch data from the new additional sheet. It should iterate dynamically and loads all the sheets' data to a single table. It will do always truncate and load.

Answer 1

Hey,
Currently getting the sheetnames dynamically in ADF is not possible.
So you would have to write a custom logic to get the list of sheet names and then iterate it over foreach.

For that you can leverage Azure automation/Azure function etc and call them in ADF

https://stackoverflow.com/questions/67541195/adf-how-to-copy-an-excel-sheet-with-multiple-sheets-into-separate-csv-files