A Validation transform is very much similar to the case transform. This also comes under ‘Platform’ set of transform in data services.
It is used to validate the data and transfer it to Pass and Fail tables. The validation rules can be defined in this transform. They can be simple or complex.
The rules can be written for each single column.
One important point to mention here is that a FAIL rule is stronger that PASS rule as a row will pass once it satisfies all the conditions but it will fail if any one of the condition is not satisfied.
A Validation transform has an input and two output schema.
In the Fail validation schema, there are two extra columns, one is Error Action and the other is Error Column.
The error action column will tell whether the row is sent to pass or fail or both the schema.
The error column will have the information about which column has failed.
One extra table ‘Validation_RuleViolation’ will also be generated having the error details.
Also, there is option for ‘Action on Failure’. Here, you can direct the system to transfer the failed record to pass table, fail table or both the tables and also substitute a text in place of the failed column value.
Here, we will be using the same database as created in Case transform example.
Create a new project ‘validation_transform’. Continue reading →