Out of Memory Error exporting to Excel - 8GB RAM

[Anonymous]

Hi,
We are experiencing problems to get to export a table from SQL Server to Excel 2007. Our table has about 40k rows and 8 columns and when we try to export to excel, always crash with error "Out of memory Error ".
Our machine has 8GB RAM; if we do it through SSIS (Sql Server Integration Services) the process run successfully in a few seconds, why we can't get it with Talend?
We're using the following config for Run/Debug (modified on Preferences > Talend > Run/Debug):
-Xms1024M
-Xmx6144M
Any idea?
Thanks

[Anonymous]

check the schema of tmssqlinput and fFileinputexcel...try to mention size for string columns..
also try to export say only 10 rows initially and see whether that works..and then keep on increasing row size..see memory consumption while 10 rows job is running...
are you doing any transformation in the job..what all components you have in job.

[Anonymous]

Hi Garpit, thanks by your reply.
About the schema, the longest column is a String (100).
If i export less rows i can do it correctly.
Our job copies an excel template to other place, run a SQL procedure to generate the source table, and then, get the data rows and export to excel, without any transformation (i attach a photo)
Any idea?
Thanks

[Anonymous]

The problem is, you accumulate all data into the memory with complex structure.
You could try the user components tFileExcelWorkbookOpen (has a streaming option to limit the memory usage) + tFileExcelSheetOutput and tFileExcelWorkbookSave
With the streaming option allows you to write nearly unlimited (only limited by Excel it self) number of large rows.
The trade off of this mode is, you can only access the last 100 rows after writing them within the Talend job but if you want only writing and do not read out in the same job, this will work.

[Anonymous]

The problem is, you accumulate all data into the memory with complex structure.
You could try the user components tFileExcelWorkbookOpen (has a streaming option to limit the memory usage) + tFileExcelSheetOutput and tFileExcelWorkbookSave
With the streaming option allows you to write nearly unlimited (only limited by Excel it self) number of large rows.
The trade off of this mode is, you can only access the last 100 rows after writing them within the Talend job but if you want only writing and do not read out in the same job, this will work.

Thanks by your answer.
Could you say me where can i find that componentes? are they custom components?
Thanks!

[Anonymous]

Yes these components are published in Talend Exchange: http://www.talendforge.org/exchange/index.php
Please search for tfileexcel and you will find the whole suite.
These components works a bit different as the build-in components.
At first you need tFileExcelWorkbookOpen to open a new or existing Excel file (capable to read a file an use it as template and write it with tFileExcelWorkbookSave under a new name).
In tFileExcelWorkbookOpen you can specify the memory saving mode but this mode works only for the newer xslx format, not for the old xls format.
The other components read/write into the workbook.

[Anonymous]

I got it!! thanks you very much jlolling.
Cheers

[Anonymous]

Hi jlolling
I've another question.
When i use those components running the job manually from Studio it works fine, however, when i try to run it through TALEND ADMINISTRATOR CONSOLE (I've scheduled the exection of the job) the custom excel's components don't run and the excel is not generated, why? do i need install custom components in any place more?
Thanks for your time
Cheers

EDIT: the job's execution traces only shows the beginning and end of the whole execution, and MSSQL components, but not the excel's traces.
It appears as these componentes not achieve to run:

TALEND STUDIO'S JOB TRACES

connecting to socket on port 4045
connected
2014-02-12 18:06:24|jmrNVc|jmrNVc|jmrNVc|544|MyProject|MyJob|_owoZQImUEeO5AbT5-_XKlg|0.6|Default||begin||

2014-02-12 18:06:24|jmrNVc|jmrNVc|jmrNVc|544|MyProject|MyJob|_owoZQImUEeO5AbT5-_XKlg|0.6|Default|tFileExcelWorkbookOpen_1|begin||
2014-02-12 18:06:25|jmrNVc|jmrNVc|jmrNVc|544|MyProject|MyJob|_owoZQImUEeO5AbT5-_XKlg|0.6|Default|tFileExcelWorkbookOpen_1|end|success|1016

2014-02-12 18:06:26|jmrNVc|jmrNVc|jmrNVc|544|MyProject|MyJob|_owoZQImUEeO5AbT5-_XKlg|0.6|Default|tMSSqlRow_1|begin||
2014-02-12 18:06:26|jmrNVc|jmrNVc|jmrNVc|544|MyProject|MyJob|_owoZQImUEeO5AbT5-_XKlg|0.6|Default|tMSSqlRow_1|end|success|47

2014-02-12 18:06:26|jmrNVc|jmrNVc|jmrNVc|544|MyProject|MyJob|_owoZQImUEeO5AbT5-_XKlg|0.6|Default|tFileExcelSheetOutput_1|begin||
2014-02-12 18:06:29|jmrNVc|jmrNVc|jmrNVc|544|MyProject|MyJob|_owoZQImUEeO5AbT5-_XKlg|0.6|Default|tFileExcelSheetOutput_1|end|success|3031
2014-02-12 18:06:29|jmrNVc|jmrNVc|jmrNVc|544|MyProject|MyJob|_owoZQImUEeO5AbT5-_XKlg|0.6|Default|tFileExcelSheetOutput_2|begin||
2014-02-12 18:06:30|jmrNVc|jmrNVc|jmrNVc|544|MyProject|MyJob|_owoZQImUEeO5AbT5-_XKlg|0.6|Default|tFileExcelSheetOutput_2|end|success|1468
2014-02-12 18:06:30|jmrNVc|jmrNVc|jmrNVc|544|MyProject|MyJob|_owoZQImUEeO5AbT5-_XKlg|0.6|Default|tFileExcelWorkbookSave_1|begin||
2014-02-12 18:06:38|jmrNVc|jmrNVc|jmrNVc|544|MyProject|MyJob|_owoZQImUEeO5AbT5-_XKlg|0.6|Default|tFileExcelWorkbookSave_1|end|success|8235
2014-02-12 18:06:39|jmrNVc|jmrNVc|jmrNVc|544|MyProject|MyJob|_owoZQImUEeO5AbT5-_XKlg|0.6|Default|tFileExcelSheetOutput_3|begin||
2014-02-12 18:06:41|jmrNVc|jmrNVc|jmrNVc|544|MyProject|MyJob|_owoZQImUEeO5AbT5-_XKlg|0.6|Default|tFileExcelSheetOutput_3|end|success|2453
2014-02-12 18:06:41|jmrNVc|jmrNVc|jmrNVc|544|MyProject|MyJob|_owoZQImUEeO5AbT5-_XKlg|0.6|Default|tFileExcelSheetOutput_4|begin||
2014-02-12 18:06:43|jmrNVc|jmrNVc|jmrNVc|544|MyProject|MyJob|_owoZQImUEeO5AbT5-_XKlg|0.6|Default|tFileExcelSheetOutput_4|end|success|1453

2014-02-12 18:06:52|jmrNVc|jmrNVc|jmrNVc|544|MyProject|MyJob|_owoZQImUEeO5AbT5-_XKlg|0.6|Default||end|success|27828
disconnected
Job MyJob ended at 18:06 12/02/2014.

TALEND ADMINISTRATOR CENTER'S JOB TRACES

### Job STARTED at 2014/02/12 17:53:28 (jobId=20140212_174057_vYBXD, jobExecutionId=20140212175328_pVLLY) ###
2014-02-12 17:53:28|20140212175328_pVLLY|20140212175328_pVLLY|20140212175328_pVLLY|5676|MyProject|MyJobl|_owoZQImUEeO5AbT5-_XKlg|0.6|Default||begin||
2014-02-12 17:53:29|20140212175328_pVLLY|20140212175328_pVLLY|20140212175328_pVLLY|5676|MyProject|MyJobl|_owoZQImUEeO5AbT5-_XKlg|0.6|Default|tMSSqlRow_1|begin||
2014-02-12 17:53:29|20140212175328_pVLLY|20140212175328_pVLLY|20140212175328_pVLLY|5676|MyProject|MyJobl|_owoZQImUEeO5AbT5-_XKlg|0.6|Default|tMSSqlRow_1|end|success|78
17:53:31|20140212175328_pVLLY|20140212175328_pVLLY|20140212175328_pVLLY|5676|MyProject|MyJobl|_owoZQImUEeO5AbT5-_XKlg|0.6|Default||end|success|2640
### Job ENDED SUCCESSFULLY at 2014/02/12 17:53:31 (jobId=20140212_174057_vYBXD, jobExecutionId=20140212175328_pVLLY) ###

[Anonymous]

Yes, you have to setup a user component path for the commandline in the TAC.
If you have configured it you will see the process of building a job as one step more "setUserComponentPath".
Which release do you run? Actually the commandline should complain about the missing components.
Best regards.
Jan Lolling

[Anonymous]

Thanks jlolling by your suggestion,
I've just configured my User components' path in Talend Administrator > Configuration > Commandline ( Talend Help!)
And yes, now I can see the traces of these components' execution. However, they are not running ok because they don't insert any row into the excel files. Any idea of this behaviour??
The job's traces from Talend Administrator Center:

### Job STARTED at 2014/02/13 10:06:14 (jobId=20140213_095819_kzpvS, jobExecutionId=20140213100614_ghgca) ###
2014-02-13 10:06:14|20140213100614_ghgca|20140213100614_ghgca|20140213100614_ghgca|9468|MyProject|MyJob|_owoZQImUEeO5AbT5-_XKlg|0.6|Default||begin||
2014-02-13 10:06:15|20140213100614_ghgca|20140213100614_ghgca|20140213100614_ghgca|9468|MyProject|MyJob|_owoZQImUEeO5AbT5-_XKlg|0.6|Default|tFileExcelWorkbookOpen_1|begin||
2014-02-13 10:06:15|20140213100614_ghgca|20140213100614_ghgca|20140213100614_ghgca|9468|MyProject|MyJob|_owoZQImUEeO5AbT5-_XKlg|0.6|Default|tFileExcelWorkbookOpen_1|end|success|0
2014-02-13 10:06:15|20140213100614_ghgca|20140213100614_ghgca|20140213100614_ghgca|9468|MyProject|MyJob|_owoZQImUEeO5AbT5-_XKlg|0.6|Default|tMSSqlRow_1|begin||
2014-02-13 10:06:15|20140213100614_ghgca|20140213100614_ghgca|20140213100614_ghgca|9468|MyProject|MyJob|_owoZQImUEeO5AbT5-_XKlg|0.6|Default|tMSSqlRow_1|end|success|78
2014-02-13 10:06:15|20140213100614_ghgca|20140213100614_ghgca|20140213100614_ghgca|9468|MyProject|MyJob|_owoZQImUEeO5AbT5-_XKlg|0.6|Default|tFileExcelSheetOutput_1|begin||
2014-02-13 10:06:16|20140213100614_ghgca|20140213100614_ghgca|20140213100614_ghgca|9468|MyProject|MyJob|_owoZQImUEeO5AbT5-_XKlg|0.6|Default|tFileExcelSheetOutput_1|end|success|281
2014-02-13 10:06:16|20140213100614_ghgca|20140213100614_ghgca|20140213100614_ghgca|9468|MyProject|MyJob|_owoZQImUEeO5AbT5-_XKlg|0.6|Default|tFileExcelSheetOutput_2|begin||
2014-02-13 10:06:16|20140213100614_ghgca|20140213100614_ghgca|20140213100614_ghgca|9468|MyProject|MyJob|_owoZQImUEeO5AbT5-_XKlg|0.6|Default|tFileExcelSheetOutput_2|end|success|250
2014-02-13 10:06:16|20140213100614_ghgca|20140213100614_ghgca|20140213100614_ghgca|9468|MyProject|MyJob|_owoZQImUEeO5AbT5-_XKlg|0.6|Default|tFileExcelWorkbookSave_1|begin||
2014-02-13 10:06:16|20140213100614_ghgca|20140213100614_ghgca|20140213100614_ghgca|9468|MyProject|MyJob|_owoZQImUEeO5AbT5-_XKlg|0.6|Default|tFileExcelWorkbookSave_1|end|success|0
2014-02-13 10:06:16|20140213100614_ghgca|20140213100614_ghgca|20140213100614_ghgca|9468|MyProject|MyJob|_owoZQImUEeO5AbT5-_XKlg|0.6|Default|tFileExcelSheetOutput_3|begin||
2014-02-13 10:06:17|20140213100614_ghgca|20140213100614_ghgca|20140213100614_ghgca|9468|MyProject|MyJob|_owoZQImUEeO5AbT5-_XKlg|0.6|Default|tFileExcelSheetOutput_3|end|success|203
2014-02-13 10:06:17|20140213100614_ghgca|20140213100614_ghgca|20140213100614_ghgca|9468|MyProject|MyJob|_owoZQImUEeO5AbT5-_XKlg|0.6|Default|tFileExcelSheetOutput_4|begin||
2014-02-13 10:06:17|20140213100614_ghgca|20140213100614_ghgca|20140213100614_ghgca|9468|MyProject|MyJob|_owoZQImUEeO5AbT5-_XKlg|0.6|Default|tFileExcelSheetOutput_4|end|success|156
2014-02-13 10:06:21|20140213100614_ghgca|20140213100614_ghgca|20140213100614_ghgca|9468|MyProject|MyJob|_owoZQImUEeO5AbT5-_XKlg|0.6|Default||end|success|6719
### Job ENDED SUCCESSFULLY at 2014/02/13 10:06:21 (jobId=20140213_095819_kzpvS, jobExecutionId=20140213100614_ghgca) ###

Cheers