2

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

Hadi
  • 36,233
  • 13
  • 65
  • 124
phalondon
  • 155
  • 2
  • 3
  • 15
  • 1
    Would be easier to format the data in Excel beforehand, but you can choose the option to "Skip X number of rows" in the source configuration to handle this. You will need to hard code the column "names" since some of yours are blank, just don't click the "Column names in first row" option and you should be able to specify. – Jacob H Dec 13 '17 at 20:19
  • Can't go back and edit now but I looked at the Excel source and then realized I was thinking of the Flat File source. So unless you want to save the files as csv or tab delimited first, then Hadi's solution is probably best. – Jacob H Dec 13 '17 at 20:27

1 Answers1

1

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.

Hadi
  • 36,233
  • 13
  • 65
  • 124
  • Hi Hadi, Thankyou very much. actually im not very good with SSIS, do you have any introduction with Screenshot? it would be very nice of you – phalondon Dec 13 '17 at 20:30
  • @phalondon if you read the first line of may answer @ https://stackoverflow.com/questions/47437513/importing-excel-files-having-variable-headers/47491497#47491497 it says that *`you have to read @DrHouseofSQL answer first then @BHouse answer then continue with this answer`*, they contains screenshots. – Hadi Dec 13 '17 at 20:32
  • @phalondon if you still need help, today i cannot provide a detailed step by step answer with screenshots, i really apologize but i can provide on tomorrow – Hadi Dec 13 '17 at 20:35
  • Thankyou Hadi, you are very nice. your Sceenshots is big help for me. actually i am learning with SSIS, i saw some basic video how to load Excel in SSIS. but this Excel File is complicate for me. In my real Data, there are 20 Sheet with the same Data structure like this.i have test data, if you want i can send you via Email – phalondon Dec 13 '17 at 20:40
  • @phalondon i think that the screenshots you provided are sufficient, but also if your data are not confidential you can upload them to the cloud and provide a link so you can also get help from others – Hadi Dec 13 '17 at 20:47
  • @Phalondon i am sorry i couldn't post you a detailed answer until now, but i am really busy these days. Please just try to follow the answer i provided in a link, it is a step by step answer, and it contains screenshots (only necessary)., and other steps are very basic and easy. just give it a try. and ask me on anything that you have misunderstood and you will be welcomed – Hadi Dec 15 '17 at 04:40