CODEWITHSUNDEEP
sql-serverexcelssisetl
phalondon
phalondon

Reputation: 305

How to load specific Field in Excel with SSIS

I have a Excel workbook with 2 Sheets: testdata1 and testdata2.

The data of a sheet looks like this:

enter image description here

As you see, some column header like average Test and sortnumber are not on the same row with other column header ( testvalue1, testvalue2..)

I want to load the data of Sheets testdata1 and testdata2 to a table in SQL Server.

Expected Output

The result I hope to see looks like this

enter image description here

Can you please help me how to do it?

excel data Link

Upvotes: 2

Views: 1750

Answers (1)

Hadi
Hadi

Reputation: 37313

You have to create a dynamic SQL command that read from the first line after the headers, and to fix the columns name in the Excel Source, following these steps:

  1. Add a script task before the data flow task that import the data
  2. You have to use the script task to open the excel file and get the Worksheet name and the header row
  3. Build the Query and store it in a variable
  4. in the second Data Flow task you have to use the query stored above as source (Note that you have to set Delay Validation property to true)

You can follow my answer Importing excel files having variable headers - which is a similar case - to get more details.

Upvotes: 1

Related Questions