Flat file formatting issue in SSIS

Question

We receive a flat file that is delimited from our third-party client.

• Row Delimiter = LF;
• Column Delimiter = Tab

The file has 8 columns. The delimited formatting in the file is correct for the most part except for three records where the 6th column splits and the record continues into the second row. There are two tab column delimiters after the column breaks into the second row. We use SSIS to insert the records from the file into our DB and the ETL breaks because of this inconsistent formatting. We had to manually tweak the column so that the job runs successfully. Is there a way to correct the formatting issue in SSIS? I need help with writing a parser to correct these abnormal records in the file before inserting them.

Normal Row:

Problematic rows:

Answer 1

To fix the file structure, you should read each row as one large column DT_STR (4000). Then you should use two script components: the first one to fix the erroneous rows, and the second to split each row into separate columns before inserting the data into the destination database.

You can check my answer on the following question for a step-by-step guide: SSIS reading LF as terminator when its set as CRLF