How to load specific columns of multiple spreadsheets of excel file using SSIS?

Question

I am trying to insert data from an excel sheet to SQL Server using SSIS. The sheet contain multiple spreadsheets and I have to load only specific columns and insert their data to main table.

for eg. there are 4 spreadsheets like Sheet1, Sheet2, Sheet3, Sheet4

I need 2 columns from Sheet1, 3 columns from Sheet2, 1 column from Sheet3 and 4 Columns from Sheet4 and insert their data to main table which is named as dbo.StatsOfWeek and contains all those columns that i need from above sheets.

How can i do this? I had tried some approaches but could not succeed to do what I need.

PS: I am using SQL Server 2016 and Visual Studio 2017(SSDT)

Answer 1

you can use "Merge Join component" (but it's not recommended - bad practice)

It looks like that:

steps:

1. Create excel sources.
2. Use sort component and select column/s for sorting.
3. Add "merge join" and choose "join type" and columns for output.
4. Add destination or other transforms.