There are no native transformations within an SSIS Data Flow that allow you to inspect/modify the current row based on the previous row(s) data.
In your case, you'd like to carry information from line 1 to lines 2 to N.
As @Nick.McDermaid points out, you have approximately 3 options.
The first is to just load it all into a table "as is" and then modify the data in a post load step (most likely an Execute SQL Task)
The second step is to double read the file/worksheet. The first read will pull the header data out and assign it to SSIS Variables (Script Task is easiest, RecordSet destination + Foreach Enumerator would be the code-free way to do it) and then you can use the Derived Column Transformation to inject your variables as columns.
For future readers, this approach is only going to work if the parent block remains constant. If you're parsing complex record types (mainframe, mixed header/detail records, EDI, EMRs etc) this is likely the wrong tool for you
The third step is to dive deep into .NET language of your choice and write an overly complex Script Component Source to assemble the data as needed. I covered that a bit on this post of SSIS Excel Source via Script . The general concept in the ExcelReader class is to use the JET/ACE oledb provider to read an Excel worksheet into a DataTable
Once you have the contents into a datatable, then you write your parsing logic (ExcelParser). Pull whatever data you need from the first N rows and then for each row in your data table, add an output buffer and copy the existing rows over and augment with your header data.
Pretty, it isn't but it's the only way to solve the problem when the data isn't properly tabular.
