CODEWITHSUNDEEP
sql-serverexcelssisrowcell
Nastya
Nastya

Reputation: 1

SSIS Excel definite cell with rows

I have an Excel spreadsheet like this:

C1 - 100100
G1 - 01.01.2015
H1 - 15.05.2015

From A5:

ID         animal    sales
123123       bear      100
213213       zebra     200 
321321       lion      150

and I need to move data into a SQL Server database using SSIS. The resulting table should look like

ID      Animal   Sales    Date1       Date2         Region
123123  bear     100      01.01.2015   15.05.2015   100100
213213  zebra    200      01.01.2015   15.05.2015   100100
321321  lion     150      01.01.2015   15.05.2015   100100

Any help would be greatly appreciated.

Upvotes: 0

Views: 47

Answers (1)

hcaelxxam
hcaelxxam

Reputation: 786

The way I have handled this problem in the past is to:

  • Define 2 Excel connection managers, one with FirstRowHasColumnName to True and one where that is false.
  • Create an Excel Source, use the first connection manager, and point it to your rowset TableName$A5:ZZ,
  • Create 3 more Excel Sources which will point to the rowsets TableName$C1:C1, TableName$G1:G1, and TableName$H1:H1
  • Add a derived column to each of these, set it, and then do a union based on your derived column. Do this for your original source as well.

When doing this I had problems with the metadata for the column names for the C1, G1, and H1 sources, so it took some fiddling to finally get it to work.

Hope this helps.

Upvotes: 1

Related Questions