best way to generate excel file with 40000 recrods using MSOGenerateExcel OOTB activity.
We have a requirement to generate excel sheet with 40000 records pulled from database table.
As of now we are doing it using MSOGenerateExcel OOTB activity, we are running Report definition to pull the records from DB and then copying those to pagelist property and then trying to populate those pagelist values to excel sheet.
Till 20000 records it is working fine. But for 40000 records, it is not displaying any error message on screen rather it is just displaying busy indicator for indefinite time and latter there are some thread dumps generating in log files.
We have modified max limit in report definition to 40000 and time also increased in data access tab.
We are using tomcat server.
please let me know what we can modify to get it work with 40000 records?
Using bix is not feasible for us in this business scenario.
As of now we have very few records in production below 100. so user clicking on link which downloads the excel file, then user doing required modifications and then uploading the same file using other feature.
If we use BIX, user can't access the file immediately as they will not have the access to any physical location in server.