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

Challagundla, Sindhura (ITS) 0 Reputation points
2024-05-31T02:07:30.6933333+00:00

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!

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,183 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,502 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. ZoeHui-MSFT 34,756 Reputation points
    2024-05-31T05:33:17.3+00:00

    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?