best practice for ragged delimited text import

I have tab delimited text files which may have optional fields (meaning they can be not present at all) to the right of the required fields that I care about. It would appear that using a Flat File Connection with Delimited Format (tab) set will choke if it is initially configured with a file that has something like:

data\tdata\tdata\r\n

and it then encounters

data\tdata\tdata\toptionaldata\r\n

It chokes. I know this could be parsed line by line, but that seems silly. It seems like there should be a way to ignore columns beyond a certain point (e.g. Format "Delimited Ragged Right").

Is there some way to do this with a directly with a flat file connection?

Thanks,

--Andrew

If you are guaranteed to have at least one more column than the required ones, you can configure the columns you care about with the tab delimiter, and then a final column with the CR/LF delimiter that will get the rest of the row.

If not (and it looks like you can't, from your example), then you'll have to configure the last required column with the CR/LF delimiter. That way the last column will hold the last value that you want, and the rest of the row, if it exists. Then use a Derived Column transform with the FINDSTRING and SUBSTRING functions to get everything up to the tab.

Sorry, but I don't think there is an easier solution.