Problem Importing a Flat File in SSIS with a Carrige Return.

Question

I have a flat file that uses | to separate the fields and does not use a text indicator. (I have no control over the formatting of the flat file.)
The file contains a string field, and the data contained in it some times contains text that includes a carriage return.
When SSIS attempts to import the file, it sees the carriage return in the text field and misinterprets it to mean that the line of data is at its end and it starts a new record.
This then throws the rest of the import off and stops the process.

Note that I can't use a Derived Column Transformation function within SSIS because the data doesn't get past the Flat File Import function.

I'd appreciate any suggestions as to how to handle this issue.

Thank you!

Answer 1

Hi @Robert Oswald ,

*.csv/txt flat files always have host of problems where column delimiters, column separators, invisible characters like null terminators \0, special characters based on encoding, and line breaks are in the middle of the actual data.

The most reliable format for data feeds is XML enforced by XSD. An XSD (as a data contract between sender and receiver) will guarantee data format, enforce data quality, and prevent unexpected changes.

SSIS has XML Source Adapter for XML files intake.

Answer 2

Hi @Robert Oswald ,

Go to Flat File Connection Manager property-->AlwaysCheckForRowDelimiters-->False.

Check my flat file and the result in SSIS below.

If I misunderstand your issue, please incorrect me.

Regards，

Zoe

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.