excel - How to insert column name in the destination table in ssis? -

!reference question1

as shown in image... have excel sheet, contains 32 tables 1 after other (i have taken 2 tables in image) may grow table count... metadata same tables.table has 2 columns 1 constant(name) & 1 change(tpa,tpb.. etc) there no change in column position. problem how hold header , inserted t_type value destination table ?

the no of rows in each table not fixed( can't go cell reference).

the problem understand it

i believe have data in excel looks approximately

name | tpa abc  | x ...  name | tpb acz  | p 

the data described blocks of data. block defined bounded starting row value of name in it. next cell on row contain value applies subsequent rows.

after header row, need pull out key value pairs , write them plus table name destination.

the meta data remains consistent, it's source data banjaxed.

resolution

this problem had overcome when wrote ssis excel source via ssis. had source our data feeds reports instead of clean tabular data. using approach, define equivalent parsesample method , there in foreach loop (line 71 of excelparser) you'd put in logic of block field value of 'name' until encounter empty row.

psuedocode approximate

# enumerate through source data foreach row in source data     # assign values local variables     col0 = row[0]     col1 = row[1]       # test end of block     if col0 == "name"         tablename = col1     else if col0 == string.empty         # nothing     else         newrow = datatable.newrow()         newrow[0] = col0         newrow[1] = tablename         newrow[2] = col1         datatable.add(newrow) 

if want simplify matter, can have parsing logic in scriptmain , dispense data table nonsense.

upside there'd less code, downside debugging scripts devil in ssis pre-2012. it's still kludgey in 2012 it's better nothing came before it.