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

0
votes

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)

1
sql-serverexcelssissql-server-data-tools
You want to put multiple sheets into one table? You need to work out how you will associate the rows. i.e. should all rows with the same line number in excel appear in the same record in the table? – Nick.McDermaid
Yes, the number of row in multiple spreadsheets are same – Puran Kandpal
is there a key between these sheets to join multiple sources together? for example a personKey or a VendorID? I guess @Nick.McDermaid asked this question in a different way. – KeithL
Yes, The Datetime column is working as a key column in all sheets. – Puran Kandpal

1 Answers

0
votes

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

It looks like that:

Merge join with two excel source

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.