Here is the example where we set up a data flow to replace values that have incorrect formats before the data is loaded into the target.
Here we have taken the customer excel file having the data but the ZIP field has some data which is greater than length 6( in Row 9) so we have to restrict that from going to target and wanted to capture in different table. Drag the CUSTDIM file in the workspace.
Click the Transforms tab and expand the Platform category in the object library then drag and drop the validation transform to the right of the Customer table in the workspace. Drag the Template Tables icon to the right of the Validation transform in the workspace as a target.
Connect the Validation transform to the target and select the Pass option to pass all rows to this
Double-click the Validation transform.
Click on the Zip field and Enable Validation. When we enable a validation a check mark is appeared next to the column.
For Action on Failure, set it to Send to Pass because we want to pass the row to the target table
even if it fails and Type ‘INVALID’ for the ZIP code having length more than 6.
In the match pattern tab type 99999.00000 because we want ZIP to be of length 6. Validate the rule and then execute the job.
Result is :
If we want to capture the invalid values to the different table so that we can analysis it later on. So we do the following changes to the existing Data Flow.
Add the template table to the right of validation transform and select the fail option as in screenshot:
Double click the validate transform and from the Action on Failure tab, select Send To Fail, because we will send the rows that fail the rule ZIP code has the length of 6.
At the top of designer, click the Audit button
Right click on CUSTDIM and select Count.
Right click on validation_good and select Count
Then click on the rule tab and click on ADD
Then add the expression :
$Count_CUSTDIM = $Count_validation_good
In the Action Failure tab, remove the tick mark from the Raise Exception because we have to capture the records even if the row fails the audit rule.
Close the Audit window and validate all.
Run the job.
Result: The validation_bad captures the invalid record.