AnsweredAssumed Answered

[SOLVED] Problem writing from multiple data sources in one transformation to Azure SQL database

Question asked by Gert Wieland on Jan 10, 2018
Latest reply on Jan 12, 2018 by Gert Wieland

Hi,

 

I have a transformation that pulls data from several MS SQL 2016 databases and writes it into an Azure SQL database. The transformation is called from a parent transformation that iterates through multiple source databases, and writes all results from all source databases into one and the same target database, i. e. the "Table Output" operator is used for each iteration.

 

When I run it for 2 or 3 databases, everything works fine, but I need to combine and transfer data from about 20 databases. Unfortunately, I get errors when I try to run it for about 5 databases or more (each database provides roughly 10,000 records, which I think is a fairly low number).

 

For better readability, I'll post the complete error message at the very end of this post. In the "table output" operator, I tried with the "batch" option both on and off, but neither worked.

 

My "wild guess" is that the issue is that I'm using the "table output" operator too many times (with each iteration, i. e. each source database), but I don't know how to address that.

 

Here's the full error message (no further text after the error message).

 

2018/01/10 04:20:17 - dim_Clients.0 - ERROR (version 8.0.0.0-28, build 8.0.0.0-28 from 2017-11-05 07.27.50 by buildguy) : Because of an error, this step can't continue:

2018/01/10 04:20:17 - dim_Clients.0 - ERROR (version 8.0.0.0-28, build 8.0.0.0-28 from 2017-11-05 07.27.50 by buildguy) : org.pentaho.di.core.exception.KettleDatabaseException:

2018/01/10 04:20:17 - dim_Clients.0 - Unable to clear batch for prepared statement

2018/01/10 04:20:17 - dim_Clients.0 - The connection is closed.

2018/01/10 04:20:17 - dim_Clients.0 -

2018/01/10 04:20:17 - dim_Clients.0 - at org.pentaho.di.core.database.Database.clearBatch(Database.java:1347)

2018/01/10 04:20:17 - dim_Clients.0 - at org.pentaho.di.trans.steps.tableoutput.TableOutput.writeToTable(TableOutput.java:338)

2018/01/10 04:20:17 - dim_Clients.0 - at org.pentaho.di.trans.steps.tableoutput.TableOutput.processRow(TableOutput.java:125)

2018/01/10 04:20:17 - dim_Clients.0 - at org.pentaho.di.trans.step.RunThread.run(RunThread.java:62)

2018/01/10 04:20:17 - dim_Clients.0 - at java.lang.Thread.run(Thread.java:748)

2018/01/10 04:20:17 - dim_Clients.0 - Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The connection is closed.

2018/01/10 04:20:17 - dim_Clients.0 - at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:191)

2018/01/10 04:20:17 - dim_Clients.0 - at com.microsoft.sqlserver.jdbc.SQLServerConnection.checkClosed(SQLServerConnection.java:710)

2018/01/10 04:20:17 - dim_Clients.0 - at com.microsoft.sqlserver.jdbc.SQLServerStatement.checkClosed(SQLServerStatement.java:1071)

2018/01/10 04:20:17 - dim_Clients.0 - at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.clearBatch(SQLServerPreparedStatement.java:1808)

2018/01/10 04:20:17 - dim_Clients.0 - at org.pentaho.di.core.database.Database.clearBatch(Database.java:1345)

2018/01/10 04:20:17 - dim_Clients.0 - ... 4 more

2018/01/10 04:20:17 - dim_Clients.0 - ERROR (version 8.0.0.0-28, build 8.0.0.0-28 from 2017-11-05 07.27.50 by buildguy) : Unexpected error committing the database connection.

2018/01/10 04:20:17 - dim_Clients.0 - ERROR (version 8.0.0.0-28, build 8.0.0.0-28 from 2017-11-05 07.27.50 by buildguy) : org.pentaho.di.core.exception.KettleDatabaseException:

2018/01/10 04:20:17 - dim_Clients.0 - Unable to empty ps and commit connection.

2018/01/10 04:20:17 - dim_Clients.0 - The connection is closed.

2018/01/10 04:20:17 - dim_Clients.0 -

2018/01/10 04:20:17 - dim_Clients.0 - at org.pentaho.di.core.database.Database.emptyAndCommit(Database.java:1424)

2018/01/10 04:20:17 - dim_Clients.0 - at org.pentaho.di.trans.steps.tableoutput.TableOutput.dispose(TableOutput.java:586)

2018/01/10 04:20:17 - dim_Clients.0 - at org.pentaho.di.trans.step.RunThread.run(RunThread.java:97)

2018/01/10 04:20:17 - dim_Clients.0 - at java.lang.Thread.run(Thread.java:748)

2018/01/10 04:20:17 - dim_Clients.0 - Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The connection is closed.

2018/01/10 04:20:17 - dim_Clients.0 - at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:191)

2018/01/10 04:20:17 - dim_Clients.0 - at com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData.checkClosed(SQLServerDatabaseMetaData.java:126)

2018/01/10 04:20:17 - dim_Clients.0 - at com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData.supportsBatchUpdates(SQLServerDatabaseMetaData.java:2165)

2018/01/10 04:20:17 - dim_Clients.0 - at org.pentaho.di.core.database.Database.emptyAndCommit(Database.java:1395)

2018/01/10 04:20:17 - dim_Clients.0 - ... 3 more

2018/01/10 04:20:17 - dim_Clients.0 - ERROR (version 8.0.0.0-28, build 8.0.0.0-28 from 2017-11-05 07.27.50 by buildguy) : org.pentaho.di.core.exception.KettleDatabaseException:

2018/01/10 04:20:17 - dim_Clients.0 - Unable to empty ps and commit connection.

2018/01/10 04:20:17 - dim_Clients.0 - The connection is closed.

2018/01/10 04:20:17 - dim_Clients.0 -

2018/01/10 04:20:17 - dim_Clients.0 - at org.pentaho.di.core.database.Database.emptyAndCommit(Database.java:1424)

2018/01/10 04:20:17 - dim_Clients.0 - at org.pentaho.di.trans.steps.tableoutput.TableOutput.dispose(TableOutput.java:586)

2018/01/10 04:20:17 - dim_Clients.0 - at org.pentaho.di.trans.step.RunThread.run(RunThread.java:97)

2018/01/10 04:20:17 - dim_Clients.0 - at java.lang.Thread.run(Thread.java:748)

2018/01/10 04:20:17 - dim_Clients.0 - Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The connection is closed.

2018/01/10 04:20:17 - dim_Clients.0 - at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:191)

2018/01/10 04:20:17 - dim_Clients.0 - at com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData.checkClosed(SQLServerDatabaseMetaData.java:126)

2018/01/10 04:20:17 - dim_Clients.0 - at com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData.supportsBatchUpdates(SQLServerDatabaseMetaData.java:2165)

2018/01/10 04:20:17 - dim_Clients.0 - at org.pentaho.di.core.database.Database.emptyAndCommit(Database.java:1395)

2018/01/10 04:20:17 - dim_Clients.0 - ... 3 more

2018/01/10 04:20:17 - dim_Clients.0 - ERROR (version 8.0.0.0-28, build 8.0.0.0-28 from 2017-11-05 07.27.50 by buildguy) : Unexpected error rolling back the database connection.

2018/01/10 04:20:17 - dim_Clients.0 - ERROR (version 8.0.0.0-28, build 8.0.0.0-28 from 2017-11-05 07.27.50 by buildguy) : org.pentaho.di.core.exception.KettleDatabaseException:

2018/01/10 04:20:17 - dim_Clients.0 - Error performing rollback on connection

2018/01/10 04:20:17 - dim_Clients.0 - The connection is closed.

2018/01/10 04:20:17 - dim_Clients.0 -

2018/01/10 04:20:17 - dim_Clients.0 - at org.pentaho.di.core.database.Database.rollback(Database.java:905)

2018/01/10 04:20:17 - dim_Clients.0 - at org.pentaho.di.core.database.Database.rollback(Database.java:883)

2018/01/10 04:20:17 - dim_Clients.0 - at org.pentaho.di.trans.steps.tableoutput.TableOutput.dispose(TableOutput.java:621)

2018/01/10 04:20:17 - dim_Clients.0 - at org.pentaho.di.trans.step.RunThread.run(RunThread.java:97)

2018/01/10 04:20:17 - dim_Clients.0 - at java.lang.Thread.run(Thread.java:748)

2018/01/10 04:20:17 - dim_Clients.0 - Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The connection is closed.

2018/01/10 04:20:17 - dim_Clients.0 - at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:191)

2018/01/10 04:20:17 - dim_Clients.0 - at com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData.checkClosed(SQLServerDatabaseMetaData.java:126)

2018/01/10 04:20:17 - dim_Clients.0 - at com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData.supportsTransactions(SQLServerDatabaseMetaData.java:1980)

2018/01/10 04:20:17 - dim_Clients.0 - at org.pentaho.di.core.database.Database.rollback(Database.java:891)

2018/01/10 04:20:17 - dim_Clients.0 - ... 4 more

2018/01/10 04:20:17 - dim_Clients.0 - Finished processing (I=0, O=0, R=5000, W=0, U=0, E=1)

2018/01/10 04:20:17 - dim_Clients - Transformation detected one or more steps with errors.

2018/01/10 04:20:17 - dim_Clients - Transformation is killing the other steps!

2018/01/10 04:20:17 - Select values 10.0 - Finished processing (I=0, O=0, R=5000, W=5000, U=0, E=0)

2018/01/10 04:20:17 - Delete.0 - Finished processing (I=0, O=0, R=5001, W=5000, U=5001, E=0)

2018/01/10 04:20:18 - DIM_Clients.0 - Finished processing (I=0, O=0, R=7, W=0, U=0, E=0)

2018/01/10 04:20:18 - Spoon - The transformation has finished!!

Outcomes