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

[PBuongiovanni]

Hi all,

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.

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:

The strange thing is that the problem occurs sometimes, while other times the job runs without problems.

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.

Anyone have any ideas? I've tried everything but I still haven't figured out the cause of the problem.

Thank you very much in advance.

Piergiorgio

[Anonymous]

@Piergiorgio Buongiovanni​ How many rows of data? Reduce the number of rows could reproduce the problem?

[PBuongiovanni]

Hi,

yes, the problem could be related to the number of rows of the query. 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.

[PBuongiovanni]

The strange thing is that the job runs without problems in intetìractive 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.

[PBuongiovanni]

Let me add other details that could be useful to understand the cause of this problem.

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.

The problem therefore occurs only with this job, which executes an SQL query using a connection to PostgreSQL and it does not occur when run in interactive mode.

It could be a bug? Any ideas?

[Bobby_UdeS]

Hi! Just a thought here.

Have you looked in the log file of the job to see what happens?  I've had something similar happened to me.  The database connection would be so slow to answer that my job seemed to hang.  (I added a connection timeout and it solved the problem - at least the job would not hang but would throw an error.)

[PBuongiovanni]

I resolved my problem posting my question on https://stackoverflow.com/ and that community helped me.

Somebody suggested me that maybe I am running low on heap space. I tried bumping up -Xmx#### a bit on the configuration of the caller job.

[jlolling]

Can you check what happens if you do not connect the next subjob with OnComponentOk starting from tFileExcelOutput but instead use OnSubjobOk starting from the tDBInput component please.

[jlolling]

I just wondering, the job should throw an Error in this case and not silently stop working.

Also this has nothing to do with the copy action.

[PBuongiovanni]

Same behaviour. Already tried and nothing changes.