SSIS Excel Destination is Empty

0
votes

I'm having an issue exporting a large dataset (500k+) to Excel via SSIS, where the output file ends up with 0 rows exported. Before saying that I shouldn't be exporting that many records to Excel, let me state that I know and normally wouldn't. Accounting does not want a CSV and is unwilling to open a CSV in Excel.

Using Visual Studio 2012 SSDT, here are the components involved.

  1. Execute SQL Task -> Creates the empty file with headers
  2. Data Flow Task ->
    • OLE DB Source -> SQL Query
    • Excel Destination

While the package is running, you can see records flowing from the source to the destination. The package completes without error, but when you open the file, it's empty. The only thing in there is the header.

If I select the Top 1000 records and export to Excel, it works as intended.

Some things I've tried:

  1. Export to Excel on the network
  2. Export to Excel locally
  3. Export to CSV to Excel on both network and locally
  4. Export to Ole DB Destination using Office Access Database Engine 12.0 with "Excel 12.0" extended properties.
  5. Tried running as different users

All with the same outcome.

Can anyone provide any insight into why this may be happening and how to proceed?

1
sql-serverexcelssisetl
Before asking your first question, it is recommended to read the Tour Page to learn more about asking good questions, accepting answers and other website rules. Also to get your the "informed" badge. – Hadi
Try create the excel manually then run the ssis package, it is giving the same result? Also try creating the table from the destination oakdome.com/programming/SSIS_DataTransform.php – Hadi
IF so, then make sure that there is no Excel process still running. Open the Task Manager and check for running EXCEL processes – Hadi
Not very sure, but can this be caused because of data error? – Prabhat G
@PrabhatG I was not able to find any data issues. – mikeDB

1 Answers

0
votes

We experienced a similar behaviour, when runnig the ETL in a SQL Server Agent job. Debugging it in Visual Studio, worked, however. So I do not know, whether this solution applies to you.

The reason was that the user, under which the package ran, did not have access to C:\users\Default.

I found this out by using sysinternals process monitor.

I was inspired by that post: Empty Excel File permissions issue: SSIS Excel Destination buffers large record sets through C:\Users\Default

[I explained my search for the bug in my blog: https://www.csopro.de/biblog/2018/04/ssis-fehlerbehebung-bei-excel-destination-schreibt-keine-zeilen/ Unfortunately it is in German]