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

1
votes

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?

2
sqletlflat-filessis
If at all possible, I would have the provider clean up this mess before sending it to you. We send files like that back to the clients and vendors and about 60% of the time, they fix them. May not be opossible but if they will, it sure saves you a lot fo development time. Usually when we tell a client how much less it cost them to provide a file in a decent format, they are more willing to do so. – HLGEM

2 Answers

3
votes

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?

1
votes

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.