SSIS package executed from SQL Server Agent doesn't export full data to Excel

Question

I have a SSIS package which exports data to multiple tabs to Excel, This doesn't export full data to Excel when run from SQL Server agent on server. It is checked to run as 32 bit. I have other packages which exports data to Excel and they all work fine but less data. I have only issue with this package. The highest number of rows it exports data to the tab is 22k.

There are 6 tabs that needs to be populated.

Tried following on the server:

1. The proxy account that executes the job has full access to C:\users\default
2. There is enough space on C drive.
3. Tried logging the errors when the job gives unexpected termination, but no new info was noticed.
4. No errors noticed in Windows Application log

Connection String for Excel: Provider=Microsoft.ACE.OLEDB.12.0;Data Source=tp\Rem.xlsx;Extended Properties="EXCEL 12.0 XML;HDR=YES"; Excel Version selected on Excel destination: Excel 2007-2010

Admin had installed from this link on the server recently: microsoft.com/en-us/download/details.aspx?id=54920

On devserver, they installed 32 bit.

Please advise. Thanks!

Answer 1

Hi @Challagundla, Sindhura (ITS),

As you said, it seems that there is no error message.

If you run the package via Visual Studio, will the data lose as well?

When you preview the excel source in the package, will it show all the data?