This job loads records into a SQL Server table from a spreadsheet. The database has a unique constraint on 'businessName'. If you run the job once, 5 records are inserted. If you run the job a second time on the same input, an error is generated.
|Job Featuring tWarn and a Run If to Report DB Errors|
|Error is Logged, but Processing Continues|
The job uses a global exception handler subjob (tLogCatcher, etc) that can be reused among the flows in the job. The mechanism by which the tMSSqlOutput component communicates with the exception handler is tWarn. tWarn is invoked by logic in the Run If connector.
|Logical in the Run If Connector|
For a detailed walkthrough of the job, watch this video.
Getting More Info
If you're willing to turn off batching, you can set up a rejects flow from the tMSSqlOutput, spooling rejected records off to a file. The following revision produces the file attachment as part of the data flow (Excel -> MS Sql). Batching MUST be turned off in order for the Rejects connector to be an option in the job. Uncheck the "Use Batch Size" checkbox on the Advanced Settings tab.
|Spool Rejected Records to a File; Run If Triggers a Warn|
The Run If connector provides a way to check for a result. This example looked at the _INSERTED variable of the tMSSqlOutput component. The logic could be expanded to AND or OR other conditions such correlating a row count with the number of inserts. tWarn can send the result of the check to a globally-defined exception handler. That way, multiple tests can be done in the Talend job all using the same exception code.