tFileOutputExcel doesn't work in "Append" mode when I previously create the file with tFileCopy

Question

I post a strange issue that I am experiencing with a Talend job, when I try to write data to an Excel file previously created.

Referencing to the picture below, I create an Excel file from a template using a "tFileCopy" component (block "create_file_from_template" in the picture below). I need to do this because I have an Excel file with two header lines, with the cells of the appropriate color.

Create file from template

The creation of the Excel file always run without problems. Alfter the file is successfully created a custom SQL function is called (the block "advance_task" in the picture above). The sub-job in yellow color is the block that sometimes creates problems. I read data from a PostgreSQL database using a "tDBInput" component. After this component I put a "tMap" component but it could be the same without it. The data are written into the Excel file previously created. The settings are very simple, as you can see in the picture below:

tFileOutputExcel configuration

When the data are not written to the Excel file, the job does not progress and remains at the yellow block, without producing errors. There are no errors at system level either.

When does the problem occur?

1. the job runs without problems in interactive mode from Talend Open Studio, even if the query extracts 10.000-12.000 rows. The problem occurs when the job is scheduled and runs in background.
2. the problem could be related to the number of rows extracted. When I have many rows - let me say 7.000-8.000 rows - the likelihood of the problem occurring increases. I assumed that, due to the query response time, which is directly proportional to the number of rows extracted, the file is hooked to the stream but the stream does not immediately produce the data stream.
3. This problem only occurs when using a PostgreSQL connection. It does not occur when using a connection with MS SQL Server. I've created other jobs just like this one, which use an existing Excel file (with two header lines), make a copy of it, and then use it as the output of an SQL query.

Anyone have any ideas? I've tried everything but I still haven't figured out the cause of the problem. I posted this issue on Talend Community (now Qlik Community), but nobody gave me a feedback/solution until now.

Thank you very much in advance

Answer 1

Maybe you are running low on heap space. You could try bumping up -Xmx#### a bit. I was going to tell you how to do that in the Studio, but that would likely be different than the standalone run.