I have a flat file that looks something like this:
junk I don't care about \n
\n
columns names\n
val1 val2 val3\n
val1 val2 val3\n
columns names \n
val1 val2 val3\n
I only care the lines with values. These value lines are all fixed width format and have the same line length. The other junk lines and column names can have any line width.
When I try the flat file fixed width option or the ragged right option the preview looks all wrong. Any ideas what the easiest way to get this into SSIS is?
You cannot use the fixed width option and I seem to recall that the ragged right option only applies if the raggedness is in the entire last column.
You can use the ragged right option and read the entire thing into a string column and then use derived columns.
Alternatively, pre-process the file (possibly in SSIS, using a ragged-right with a conditional split, outputting to a flat file) to filter out the lines you are going to ignore and then you can use the flat file connection manager on the resulting file.
Another option is to code a data source script task by hand.
It would be nice if you could use more complex files by being able to define new connection manager layouts on the outputs of other data flows, but that is not currently available in SSIS.
This is basically the same problem I posed in this question: How to process ragged right text files with many suppressed columns in SSIS or other tool?