topic Re: tFileOutputExcel doesn't work in "Append" mode when I previously create the file with tFileCopy in Talend Studio

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

PBuongiovanni — Mon, 22 Jan 2024 08:26:45 GMT

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

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

Anonymous — Mon, 22 Jan 2024 08:58:09 GMT

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

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

PBuongiovanni — Mon, 22 Jan 2024 09:20:06 GMT

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.

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

PBuongiovanni — Tue, 23 Jan 2024 09:23:59 GMT

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.

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

PBuongiovanni — Fri, 26 Jan 2024 07:31:49 GMT

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?

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

Bobby_UdeS — Wed, 31 Jan 2024 21:47:35 GMT

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.)

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

PBuongiovanni — Mon, 26 Feb 2024 07:37:38 GMT

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.

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

jlolling — Mon, 26 Feb 2024 09:20:42 GMT

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.

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

jlolling — Tue, 27 Feb 2024 14:45:27 GMT

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.

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

PBuongiovanni — Tue, 27 Feb 2024 15:05:59 GMT

Same behaviour. Already tried and nothing changes.

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

PBuongiovanni — Tue, 27 Feb 2024 15:09:00 GMT

Exactly. I expected an error from the job.

On the contrary the work ends silently, without reporting anything and without carrying out the subsequent steps.

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

jlolling — Tue, 27 Feb 2024 21:18:01 GMT

An out of memory error is a tricky thing. We never can be sure who is responsible. Could you please check the database settings of the tDBInput component for Cursor size larger than 0 (actually it is the fetch size, Talend choose the wrong label). In case of MySQL the streaming mode must be set in advanced settings. The problem is, a lot of JDBC drivers keep the records in memory before deliver them. This leads to a huge amount of memory usage.