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

link管理

链接快照平台

输入网页链接，自动生成快照
标签化管理网页链接

相关文章推荐

3334166307 · 短剧搜吧· 3 月前 ·

英俊的大象 · 【机器学习笔记（七）】之泰坦尼克号获救问题 ...· 4 月前 ·

风流倜傥的葡萄酒 · How to rename a ...· 5 月前 ·

听话的牛腩 · 如何投诉京东仓库管理人员 | ...· 6 月前 ·

安静的李子 · 基于AI的蛋白质设计与改造 - ...· 8 月前 ·

Start a Discussion

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!!

#Pentaho
#PentahoDataIntegrationPDI
#Kettle
I figured it out, the error was in the design of my transformation. Since I put the question here, I figured I might as well post my solution. (I guess I'm not yet at a point to actually recommend my solution, so I'm just sharing what worked for me ).

I included a "delete" step that deletes old data in the database before the "fact_Services" operator writes the new data. All I had to do is blocking the writing operator until the delete operator is done.