Since the Budget data can be spread across multiple sheets in the Excel file, we will need to import data from the individual sheets and consolidate these into one data set.
At first, we will need a list of the sheets in the Excel file. This is obtained with an Import task with a simple SQL query:
- SELECT * FROM sys.sheets
Important: Notice the Query type: Query. (Not Table.)
With the Import task named ExcelSheetsList, we can use the Query Tool to look up the content of the similarly named InMemory table: