Running Multiple Stored Procedures with Error Handling in Talend

Let's say you want to run a string of stored procedures. They could be long running so you don't want to baby sit them. You want to get error notifications if they error out and stop execution if anyone in the chain fails.

You have multiple options:
1. Write a script that does that and pipe the whole output to "mail" command (cumbersome)
2. Write a program in Java/your favorite programming language that can make calls to the database
3. Do it in Talend.

If you're already using Talend, I suggest doing it in Talend as it is super simple. You need to create a tFixedFlowInput and put the calls to stored procedures as Inline Content. Then connect the output of that to tMysqlRow and finally, connect the output of that to tJavaRow (for keeping track of progress in error email).

The above job sends a success email on success and an error email on any failure. Don't forget to set "Die on Error" for the tMysqlRow. Doing that will kill your job as soon as an error is encountered.

The logcatcher listens to errors in the job. The output of which is connected to tSendMail which sends an error email when any component throws an error.

tFixedFlowInput
Here is where you set the list of SQL statements that you want to execute  as Inline Content
e.g. "call sproc1(val1,val2);
truncate sometable;"

tMysqlRow
Here is where you simply execute what is being sent over in the pipeline

tJavaRow
Here is where you set a global variable that keeps track of the progress of the job.

tSendMail(Send Error Email to Admins)

Here is where you fetch the variable from the global map to generate the email