Strange error producing by Kettle

Question

1. It is only a simplest tran developed in Kettle 5.4.
2. The tran extracts data from MSSQL and insert into another MSSQL without any other operations.
3. I enabled the "Use batch update for inserts" check box.
4. Usually it runs successfully with any error messages.
5. This error occurs occasionally and it can't be reproduced by rerunning the tran.

Here's the error log:

2016/09/18 08:00:36 - tb_dim_innchangelog.0 - SQL query : SELECT
2016/09/18 08:00:36 - tb_dim_innchangelog.0 -   StatID
2016/09/18 08:00:36 - tb_dim_innchangelog.0 - , ChainID
2016/09/18 08:00:36 - tb_dim_innchangelog.0 - , ChainName
2016/09/18 08:00:36 - tb_dim_innchangelog.0 - , SaleDepartID
2016/09/18 08:00:36 - tb_dim_innchangelog.0 - , AcivmntRegion
2016/09/18 08:00:36 - tb_dim_innchangelog.0 - , Category
2016/09/18 08:00:36 - tb_dim_innchangelog.0 - , Stage
2016/09/18 08:00:36 - tb_dim_innchangelog.0 - , BeginDate
2016/09/18 08:00:36 - tb_dim_innchangelog.0 - , EndDate
2016/09/18 08:00:36 - tb_dim_innchangelog.0 - , createtime
2016/09/18 08:00:36 - tb_dim_innchangelog.0 - , ChainFlag
2016/09/18 08:00:36 - tb_dim_innchangelog.0 - , LastUpdateDate
2016/09/18 08:00:36 - tb_dim_innchangelog.0 - FROM Dim_InnChangeLog
2016/09/18 08:00:36 - load_dim_innchangelog.0 - Prepared statement : INSERT INTO Dim_InnChangeLog (StatID, ChainID, ChainName, SaleDepartID, AcivmntRegion, Category, Stage, BeginDate, EndDate, createtime, ChainFlag, LastUpdateDate) VALUES ( ?,  ?,  ?,  ?,  ?,  ?,  ?,  ?,  ?,  ?,  ?,  ?)
2016/09/18 08:00:36 - tb_dim_innchangelog.0 - Finished reading query, closing connection.
2016/09/18 08:00:36 - BussinessDW_Source - Connection to database closed!
2016/09/18 08:00:36 - tb_dim_innchangelog.0 - 完成处理 (I=19147, O=0, R=0, W=19147, U=0, E=0
2016/09/18 08:00:56 - load_dim_innchangelog.0 - ERROR (version 5.4.0.1-130, build 1 from 2015-06-14_12-34-55 by buildguy) : Because of an error, this step can't continue: 
2016/09/18 08:00:56 - load_dim_innchangelog.0 - ERROR (version 5.4.0.1-130, build 1 from 2015-06-14_12-34-55 by buildguy) : org.pentaho.di.core.exception.KettleDatabaseException: 
2016/09/18 08:00:56 - load_dim_innchangelog.0 - Error performing rollback on connection
2016/09/18 08:00:56 - load_dim_innchangelog.0 - Invalid state, the Connection object is closed.
2016/09/18 08:00:56 - load_dim_innchangelog.0 - 
2016/09/18 08:00:56 - load_dim_innchangelog.0 -     at org.pentaho.di.core.database.Database.rollback(Database.java:867)
2016/09/18 08:00:56 - load_dim_innchangelog.0 -     at org.pentaho.di.core.database.Database.rollback(Database.java:845)
2016/09/18 08:00:56 - load_dim_innchangelog.0 -     at org.pentaho.di.trans.steps.tableoutput.TableOutput.writeToTable(TableOutput.java:332)
2016/09/18 08:00:56 - load_dim_innchangelog.0 -     at org.pentaho.di.trans.steps.tableoutput.TableOutput.processRow(TableOutput.java:118)
2016/09/18 08:00:56 - load_dim_innchangelog.0 -     at org.pentaho.di.trans.step.RunThread.run(RunThread.java:62)
2016/09/18 08:00:56 - load_dim_innchangelog.0 -     at java.lang.Thread.run(Thread.java:722)
2016/09/18 08:00:56 - load_dim_innchangelog.0 - Caused by: java.sql.SQLException: Invalid state, the Connection object is closed.
2016/09/18 08:00:56 - load_dim_innchangelog.0 -     at net.sourceforge.jtds.jdbc.ConnectionJDBC2.checkOpen(ConnectionJDBC2.java:1699)
2016/09/18 08:00:56 - load_dim_innchangelog.0 -     at net.sourceforge.jtds.jdbc.ConnectionJDBC2.rollback(ConnectionJDBC2.java:2100)
2016/09/18 08:00:56 - load_dim_innchangelog.0 -     at org.pentaho.di.core.database.Database.rollback(Database.java:858)
2016/09/18 08:00:56 - load_dim_innchangelog.0 -     ... 5 more
2016/09/18 08:00:56 - load_dim_innchangelog.0 - ERROR (version 5.4.0.1-130, build 1 from 2015-06-14_12-34-55 by buildguy) : Unexpected batch update error committing the database connection.
2016/09/18 08:00:56 - load_dim_innchangelog.0 - ERROR (version 5.4.0.1-130, build 1 from 2015-06-14_12-34-55 by buildguy) : org.pentaho.di.core.exception.KettleDatabaseBatchException: 
2016/09/18 08:00:56 - load_dim_innchangelog.0 - Error updating batch
2016/09/18 08:00:56 - load_dim_innchangelog.0 - Invalid state, the Connection object is closed.
2016/09/18 08:00:56 - load_dim_innchangelog.0 - 
2016/09/18 08:00:56 - load_dim_innchangelog.0 -     at org.pentaho.di.core.database.Database.createKettleDatabaseBatchException(Database.java:1386)
2016/09/18 08:00:56 - load_dim_innchangelog.0 -     at org.pentaho.di.core.database.Database.emptyAndCommit(Database.java:1378)
2016/09/18 08:00:56 - load_dim_innchangelog.0 -     at org.pentaho.di.trans.steps.tableoutput.TableOutput.dispose(TableOutput.java:575)
2016/09/18 08:00:56 - load_dim_innchangelog.0 -     at org.pentaho.di.trans.step.RunThread.run(RunThread.java:96)
2016/09/18 08:00:56 - load_dim_innchangelog.0 -     at java.lang.Thread.run(Thread.java:722)
2016/09/18 08:00:56 - load_dim_innchangelog.0 - Caused by: java.sql.SQLException: Invalid state, the Connection object is closed.
2016/09/18 08:00:56 - load_dim_innchangelog.0 -     at net.sourceforge.jtds.jdbc.TdsCore.checkOpen(TdsCore.java:452)
2016/09/18 08:00:56 - load_dim_innchangelog.0 -     at net.sourceforge.jtds.jdbc.TdsCore.clearResponseQueue(TdsCore.java:727)
2016/09/18 08:00:56 - load_dim_innchangelog.0 -     at net.sourceforge.jtds.jdbc.JtdsStatement.initialize(JtdsStatement.java:645)
2016/09/18 08:00:56 - load_dim_innchangelog.0 -     at net.sourceforge.jtds.jdbc.JtdsStatement.executeBatch(JtdsStatement.java:908)
2016/09/18 08:00:56 - load_dim_innchangelog.0 -     at org.pentaho.di.core.database.Database.emptyAndCommit(Database.java:1362)
2016/09/18 08:00:56 - load_dim_innchangelog.0 -     ... 3 more
2016/09/18 08:00:56 - tf_dim_innchangelog - ERROR (version 5.4.0.1-130, build 1 from 2015-06-14_12-34-55 by buildguy) : 错误被检测到!
2016/09/18 08:00:56 - load_dim_innchangelog.0 - ERROR (version 5.4.0.1-130, build 1 from 2015-06-14_12-34-55 by buildguy) : Unexpected error rolling back the database connection.
2016/09/18 08:00:56 - load_dim_innchangelog.0 - ERROR (version 5.4.0.1-130, build 1 from 2015-06-14_12-34-55 by buildguy) : org.pentaho.di.core.exception.KettleDatabaseException: 
2016/09/18 08:00:56 - load_dim_innchangelog.0 - Error performing rollback on connection
2016/09/18 08:00:56 - load_dim_innchangelog.0 - Invalid state, the Connection object is closed.
2016/09/18 08:00:56 - load_dim_innchangelog.0 - 
2016/09/18 08:00:56 - load_dim_innchangelog.0 -     at org.pentaho.di.core.database.Database.rollback(Database.java:867)
2016/09/18 08:00:56 - load_dim_innchangelog.0 -     at org.pentaho.di.core.database.Database.rollback(Database.java:845)
2016/09/18 08:00:56 - load_dim_innchangelog.0 -     at org.pentaho.di.trans.steps.tableoutput.TableOutput.dispose(TableOutput.java:610)
2016/09/18 08:00:56 - load_dim_innchangelog.0 -     at org.pentaho.di.trans.step.RunThread.run(RunThread.java:96)
2016/09/18 08:00:56 - load_dim_innchangelog.0 -     at java.lang.Thread.run(Thread.java:722)
2016/09/18 08:00:56 - load_dim_innchangelog.0 - Caused by: java.sql.SQLException: Invalid state, the Connection object is closed.
2016/09/18 08:00:56 - load_dim_innchangelog.0 -     at net.sourceforge.jtds.jdbc.ConnectionJDBC2.checkOpen(ConnectionJDBC2.java:1699)
2016/09/18 08:00:56 - load_dim_innchangelog.0 -     at net.sourceforge.jtds.jdbc.ConnectionJDBC2.rollback(ConnectionJDBC2.java:2100)
2016/09/18 08:00:56 - load_dim_innchangelog.0 -     at org.pentaho.di.core.database.Database.rollback(Database.java:858)
2016/09/18 08:00:56 - load_dim_innchangelog.0 -     ... 4 more
2016/09/18 08:00:56 - load_dim_innchangelog.0 - 完成处理 (I=0, O=15000, R=18000, W=15000, U=0, E=1
2016/09/18 08:00:56 - Logging - New database connection defined
2016/09/18 08:00:56 - Logging - Attempting to find connection in Named Datasources
2016/09/18 08:00:56 - Logging - Unable to find datasource in Named Datasources. Finally will try to attempt connecting using class 
2016/09/18 08:00:56 - Logging - Connected to database.
2016/09/18 08:00:56 - Logging - Auto commit off

Answer 1

The actual error here is:

"Unexpected batch update error committing the database connection."

This may result from an incorrect data format on one of the values (e.g. trying to insert too large a number into a numeric field), ... but could also happen for no apparent reason I could find.

If you just want to avoid the error, try disabling "Use batch update for inserts" checkbox on the Table Output step, as the step might start processing the same data perfectly well without it. NB: This would be a performance trade-off, so exercise caution :)

Answer 2

This is an MSSQL error, not really a kettle error. It's difficult to determine without knowing your environment but if I had to guess I think your MSSQL configuration might be off. Maybe using the batches is taking too long and something is timing out on the connection itself? Check the keep alive setting? I'm pretty sure you're pushing something close to the threshold and it's dying on you.

Good luck!