I have multiple Excel files (Ms. Excel 2019) in multiple folders that I want to import and store the data to SQL Server table. I want to build a data warehouse for Power BI dashboard and choose SQL Server Integration Services (SSIS) in Visual Studio 2019 as ETL tools.
In Data Flow Task, I use Excel Source, set Output Error to Flat File destination, and write to OLE DB Destinations. The package run success, but only writes the flat file (.csv) to OLE DB.
I also use For Each Loop, for looping multiple Excel through multiple folders
The data write to the SQL Server table but only 1 file from 1 folder that data successfully writes to OLE DB SQL server.
Is there any way to load other files either .xlsx file or .csv file write (prefer .xlsx file) to OLE DB and store to SQL Server table?