sql server - Extracting excel files with SSIS

Question

i am trying to create a ForEachLoop container that extracts excel files within a source folder. i have created an execute sql task within a ForEachLoop container that stores my excel files full paths in an sql server table

and now i can't figure how to make it go through that list and extract each file into an ole db destination table

ps: the excel files have different types of data, columns change almost from one file to another (28 files)

can you please help me ? thank you in advance.

Answer 1

It won't work within a for each loop because your destination for each spreadsheet has to be a table that matches the columns coming in. If it was 25 different spreadsheets with the same columns types and number of columns you could insert all the rows into one table but it sounds like you need to create separate data flows for each one. You can then combine the datasource--> transform--> Ole Destination onto one data flow (which could run in parallel) and you would have (for 26 imports) three steps for each spreadsheet.