SSIS or SQL Server way of sectioning a flat file I have a few hundred thousand flat text files which contain a single column of values. The first 40 values are parameters followed by between 4000 - 8000 rows of sensor data. The files are imported once, will never change and will be accessed by parameter selection for further analysis/calculations.

I think using two tables (one for parameters, one for sensor data) would be a good schema. So I need to split the file after import, transpose the parameter rows to columns and then load all the sensor data into a single table with a key back to the row in the parameters table.

I looked at pivot and unpivot but that won't let me transpose a single row to columns.

Any help on how to do this in SSIS or SQL and thoughts on my schema appreciated I'm not sure if the database will be responsive when I get a few million rows in the sensor data table.
using ssis and some c# / vba


add a foreach loop to iterate over the files
create a variable to use for the key (increment on each iteration)
in the loop add a dataflow consisting of

a script transform (source) to read first 40 lines from the file and pivot
a derived column to add the key (from var)
a destination to the table

in the loop add a dataflow consisting of

a script transform (source) skipping the first 40 lines and reading the rest
a derived column to add the key (from var)
a destination to the table

[optional] delete or move the file with a file task


the code for pivoting would read the first 40 lines into an array then just set the output columns mapping to the array's index.

consideration may need to be given to insure the key remains unique on subsequent runs and may need initialising to the max(key) + 1 from exiting data, use an sql task for this.