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.