I came across many time with this question “How do I process data with some wrong records?”. In usual case if any error would be happened your ETL process will be stopped and if you are implementing transaction on that case whether it will be rolled back or partially committed in case if you have implemented save point but what if we want to process data and if any error record would be found skip and report that record and continue processing of remaining records.
Let’s take an example we have to process a CSV file that contains 4 records where record number 3 has some problem. Length of one field is higher than the size defined on source & target metadata. It’s clear truncation error and by default it will interrupt the process.
See the prototype of the problem and solution below:
Here we are using one flat file that has some error record (Meta Mismatch) . So this mete mismatch could be possible one source component it self or it might be it is only at target side. So the Idea is if any error would be happen redirect and report in another file and process remaining records.
CSV File Details: DataFile.csv, DestinationFail.csv, SourceFail.csv
Sample Data: Here each column Email and Value defined width is 10 string. If you will see data record number third is error record where length is more than 10 character.
Email,Value
abc@gmail,abcabc
abc2@gmail,abcabc1
abc3@gmail,abcabcabcabc2
abc4@gmail,abcabc3
Source File connection setting: Here define the length of each column 10 character bye default it would be 50
Source Component Setting: Set the error output Error and Truncation as redirect so that you can collect error record and store somewhere else for further verification.
Destination Setting: Please do the similar setting for destination component also so that if any error would be found. It could be re-directed.
Use of Script Component: Here, this component is used as transformation to get the ErrorCode and provide Error Description that is more meaningful.
Once you create an instance of Script Component on data flow it will ask to choose how you are going to use like Source, Target or Transformation. Choose Transformation.
Script Component Setting: Input Columns – Select the ErrorCode from the list so that it would be available in script.
Script Component Setting: Inputs & Outputs Column
Script Component Setting: Write the code to provide error description as output column. Select the Script component –> right click –> Edit –> Edit Script.
Write the highlighted one line in the Input0_ProcessInputRow method and you are good to capture error description based on error code.
Execute the Solution: On execution you can see that total 4 rows are extracted where three are correct and one is moved to error report and there is no interruption.
In SourceFail.csv you will find one record with error descriptions
abc3@gmail,abcabcabcabc2,-1071607675,32,The data was truncated.
Second Case where destination component has meta mismatch.
First Increase the size of Value column from 10 to 50 to avoid truncate at source and do the required meta refresh as it will impact to all down stream components.
Re run the process and this time you will find one error record after processing of destination component.
Summary: By doing setting of error re-direct we can manage to process all correct records and report incorrect report separately. There is no need to stop the process but before opting this approach you must make sure that this skip will not create any kind of inconstancy of data.
Comments