My job stops executing after just a few rows, but there are thousands

[Anonymous]

My job reads an Excel file and depending on various logic either updates or inserts records into several tables in a database.
After adding the middle stage (the one that outputs only to an Excel file for logging) to catch items with no "SOLid" but to try and match against names instead, the job just runs for a short amount of time and then stops running. I have never seen this before, what can the reason be? There is no error output either.
Thanks.

[Anonymous]

This is most likely database locking. I suspect the "email_addresses" table you've circled is using a different session than the "email_addresses" table near the bottom of the job.
go through all of your DB input/outputs and make sure they are using an existing connection-- your tMysqlConnection_1

I have done this now (and it made sense for all to use the same connection) but it didn't solve the problem.

[Anonymous]

Start by disabling all of your DB outputs.
Then re-enable them in groups until you find the output that is causing the problem. From your screenshots, it looks like its one of the outputs in the "Starting" state that is causing the issue. I would start by disabling these two "clusters" of outputs and seeing if the job runs. Then re-enable them one by one until you find the problem.
Once you know which one it is, debugging the issue will be easier.

[Anonymous]

Thank you. I agree this is the right method and I can clearly see that it is the cluster of outputs that handle the email and email-rel tables that cause the problem - there is a second tMap with a Lookup there to find out what the id of the related email address is, could this lookup itself be the problem? Let me show on a screenshot images of the job and the encircled tMap as well.

[Anonymous]

that could definitely be the issue. You may be causing deadlocking
I would echo janhess' suggestion-- break the job into smaller parts. even if you need to write a staging table to store intermediate data-- your future self will thank you for simplifying the job(s).
Just as a general rule-of-thumb: When you need to update/insert to a lookup table, do that first and commit-- then use the lookup. Then instead of dealing with weird session/timing issues you can focus on getting the data itself right

[Anonymous]

Thanks, these are good tips.
Is it possible to put a commit inbetween the "vertical levels" of my job so to speak, inbetween the tMaps? I am trying this now but getting this error message which tells me that I might have also shut down the main DB connection by doing the Commit - is this the case?

Exception in component tMysqlOutput_9
com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after statement closed.
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ...

[Anonymous]

There is a checkbox in the commit component to close the connection. I think its checked by default.

[Anonymous]

Cheers!