0

I have many excel files with fixed format. I want to read these files and insert the data into SQL server using SSIS 2008.

excel sheet format excel sheet

I want to load only the yellow cells in SQL server. in the format:

name   col1   col2   col3  col4
john   0      1      2     3
john   0      1      2     3   
john   0      1      2     3
...

I'm done loading the columns but I cant get the name "john" from the sheet. also I want to repeat the same name for all the data loaded from that excel file ( because each excel file has a different name) then insert it into the SQL server.

Can anyone help me or give me a hint !?

shdb
  • 1

1 Answers1

0

I would load the excel file twice in two different source tasks.

Once, the way you've just done in order to get the col1-col4 data. You could ignore the first rows N rows by treating them as header rows.

In a second task, you focus on the header information ("john") and just import the first two columns. Create a conditional split to weed out the row with "name" as value in the first column, discard the rest.

After that, you should be able to join in the "john"-stream with the "col1-col4" stream.

Aron Hennerdal
  • 121
  • 4
  • I tried your idea and it shows an error at reading the second source. I think it maybe because its a merge cell – shdb Nov 19 '15 at 06:06
  • what do you mean by "conditional split to weed out the row with "name" as value in the first column"? – shdb Nov 19 '15 at 10:09
  • You have a line looking like "Name John" above the actual data. You could extract this information using a conditional split. Set a condition in the conditional split on [Column1] = "name" (replace "Column1" with whatever you named the first column of excel-file) and use that output data flow to only to extract the name. You could then store this name in a variable and use it later on when parsing the rest of the data (col1-col4). – Aron Hennerdal Nov 23 '15 at 09:58