Validate multiple columns in a csv file

Question

Am loading a CSV file to a landing table , the problem is i have to check if some of the columns in the file are numeric or not , if they are not numeric the file should be sent to the error folder .If not they file must be processed to the landing table . Can i get some help , how do i achieve this using SSIS package.

Answer 1

Hi @Mfezeko Donald Sodawe,

You may use expression to check columns in the file are numeric or not.

(DT_I4)data == (DT_I4)data ? (DT_I4)data : NULL(DT_I4)

And then you may use precedence constraint to load data or remove the file.

Regards，

Zoe Hui

---

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

Answer 2

Hi @Mfezeko Donald Sodawe,

*.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 XML Task, OperationType Validate, to validate XML file against the XSD: https://learn.microsoft.com/en-us/sql/integration-services/control-flow/validate-xml-with-the-xml-task?view=sql-server-ver16