question

RobertOswald-6322 avatar image
0 Votes"
RobertOswald-6322 asked ZoeHui-MSFT rolled back

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

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!

sql-server-integration-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

YitzhakKhabinsky-0887 avatar image
0 Votes"
YitzhakKhabinsky-0887 answered RobertOswald-6322 commented

Hi @RobertOswald-6322,

*.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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you, but as I noted, I have no control over the flat file structure.

0 Votes 0 ·
ZoeHui-MSFT avatar image
0 Votes"
ZoeHui-MSFT answered ZoeHui-MSFT rolled back

Hi @RobertOswald-6322 ,

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

145991-screenshot-2021-11-03-102508.jpg

Check my flat file and the result in SSIS below.

145878-screenshot-2021-11-03-102635.jpg

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.




· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you, but it's sort of the reverse of your case. Someone will insert a carriage return in the middle of a text field causing what should be one line of data to be split into two lines.

A | B | C | D {CR}{LF}
1234 | Dear Mrs. Jones: {CR}{LF} Thank you for your service. | 20211031 | 1454124451 {CR}{LF}


Gers read as

1234 | Dear Mrs. Jones:

Thank you for your service. | 20211031 | 1454124451

Since the first field is an integer data type, the Flat File import fails.

I'm thinking maybe there's a way to programmatically replace the first pipe and every multiple of them with |" and the second pipe with "| . Thus creating text indicators that are currently absent from the file.

0 Votes 0 ·

Hi @RobertOswald-6322 ,

What's the type of the flat file, csv file or txt file?

With the data you provided, it also works fine.

146366-screenshot-2021-11-04-110700.jpg


0 Votes 0 ·