SSIS 2005 - How to Import a Fixed Width Flat File?

Question

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?

Answer 1

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: http://stackoverflow.com/questions/263809/ragged-right-text-files-with-many-suppressed-columns-into-sql-server-2005-ssis

Answer 2

Try this after removing the junk at the top manually.

1. set the task with fixed width option
2. Add columns manually to the advanced tab. Here you need to add 3 columns with each of length 4.

If it works.. Then you can use a script task to read the flat file and remove the junk before you go for the data flow task.