sql server 2008 - SSIS Unpivot including column names -

(bids on sql server 2008)

i have flat file (pipe-delimited) have parsed following format:

accountid    freetext1    freetext2    freetext3    freetext4 1            text    more text    other text   different text 2            text    more text    other text   different text 3            text    more text    other text   different text 

i need end result this:

accountid    title      thedata 1            freetext1  text 1            freetext2  more text 1            freetext3  other text 1            freetext4  different text 2            freetext1  text 2            freetext2  more text 2            freetext3  other text 2            freetext4  different text 3            freetext1  text 3            freetext2  more text 3            freetext3  other text 3            freetext4  different text 

i still rather new ssis learning go. found on unpivot transformation seems need, haven't been able figure out how unpivot based on name of column ("freetext1", etc), nor have been able grasp how set unpivot transform close desired results.

i haven't yet found ssis formulas use in derived column column name programmatically, thinking maybe generate column names in derived column , merge join 2 together... doesn't seem efficient method , couldn't make work anyway. have tried setting derived column return column names in hard code (using "freetext1" formula, example), remain unsure how combine unpivoted results.

any input appreciated!

you use unpivot transformation, should like

or load data staging table , use tsql unpivot function:

select  upvt.accountid, upvt.title, upvt.thedata    dbo.stagingtable t unpivot (title thedata in (freetext1, freetext2, freetext3, freetext4)) upvt; 

or longer winded, more flexible use cross apply along table value constructor unpivot data. e.g.

select  t.accountid, upvt.title, upvt.thedata    dbo.stagingtable t         cross apply         (values             ('freetext1', freetext1),             ('freetext2', freetext2),             ('freetext3', freetext3),             ('freetext4', freetext4)         ) upvt (title, thedata);