SSIS 2019 DTSX package is failing while loading data from a flat file have a backslash (\) in data element value

Question

We upgraded from SQL 2008 to SQL 2017 in 2022!

The SSIS 2008 R2 package was loading file from flat file (pipe delimiter) and

Flat file is ANSI Encoding

• The file has data like
• |""""|
• |"abcde "xyz""|
• |"ABC""|
• |"abcd\xyx"|

No issue while loading data using SQL SSIS 2008 R2

Since upgraded to SQL SSIS 2017, the dtsx is failing to load data from the file if we have similar data

No change in file encoding

Any idea what is causing this and how to fix that?

Thanks

Answer 1

@Syed Manzar Abbas Zaidi,

*.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. Or data elements are missing completely.

The most reliable format for data feeds is XML enforced by an XSD. An XSD plays a role of a data contract between sender and receiver. It will guarantee proper data format/shape, data types, cardinality, and enforce data quality.

SSIS has the following Tasks to handle XML files:

• XML Source Adapter to ingest data from XML files.
• XML Task, operation Validation.

Answer 2

Hi @Syed Manzar Abbas Zaidi,

Have you tried to delete the component and re-add it for a try?

I'm using VS2019, it works fine from my sdie.

Regards，

Zoe Hui

---

If the answer is helpful, please click "Accept Answer" and upvote it.