SSIS package not importing data for some files, even when entire package successful

Question

I am trying to import roughly 300 delimited UTF-8 txt files using SSMS 2019 into SQL Server 2016. However, around 88 of these files do not import any data, despite there being data in the original file. The entire package shows as successful and there are no yellow bang warnings in the package. I also used SSMS current version to import one of those files, but no data was imported and the job did not fail. Unfortunately, I cannot change the target SQL Server. What steps can I take to resolve this issue?

Answer 1

@Bill Park,

*.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 for that the following tasks:

• XML Task, operation Validation.
• XML Source Adapter.