SSIS package doesn't write data to excel destination

Question

I have an simple SSIS package and I'm trying to export same set of data from a table to both flat file and excel destination. The package works fine when I run locally and it creates both text file and excel file with data.

But when deployed to a different server the sql agent job runs fine and the log inside integrations services catalog for the package says it wrote like 9000 rows to excel, and a new excel file is also created but it doesn't write any data to it(blank with just headers). text file works fine and it has all data I need.

SSIS package flow:

I'm working with Sql server 2014, Visual studio 2013 with SSDT and used Excel 2007 in excel destination.

Answer 1

I had the same problem writing to several worksheets in an Excel file from a scheduled SQL Agent job. It worked fine for about 4 months. Then suddenly with no changes to the package, one of the 5 worksheets was no longer populated with data. No error message generated and it worked fine on every test from Visual Studio and Data Tools (the old "BIDS" tools as we used to call it.)

I never did find a solution and it continues to not write any data to that single worksheet of the 5 in the Excel file. (So answers above about the Account that the job runs under from SQL Agent does not have the appropriate permissions is NOT a correct answer for this issue.)

Plus, a new package I built today is having the same issue, only this one has only a single worksheet. Again, works fine in the development environment, but no data appears in the destination file and no errors. Not only that, but the timestamp on the file is the same as the template file -- it seems that it never even TRIES to write to the file.

Checking each run log for the package in the Integration Services Catalog has an entry in each log that shows 9K+ records "written" for the dataflow task.

Lastly, if I change the destination file name, the SQL Agent job generates the expected error, so that rules out answers that guess that the path is wrong.

This is bizarre. And exasperating.

Answer 2

We had the same issue. The solution is that the user, which runs the SSIS package, must have full access to c:\users\default.

You can check this by running sysinternals' process monitor on the machine that executes the SSIS job.

You can find more information here:

• Empty Excel File permissions issue: SSIS Excel Destination buffers large record sets through C:\Users\Default - This post made me find this solution

• https://www.csopro.de/biblog/2018/04/ssis-fehlerbehebung-bei-excel-destination-schreibt-keine-zeilen/ - my blog. Here I describe the issue - unfortunately in German]

Answer 3

I have encountered odd behaviour when using scheduled SSIS packages which use the Excel object.

The fix for me, was to edit the Agent Job properties. On the Execution Properties tab, try enabling the "use 32-bit runtime" option and force the SSIS to run in 32-bit mode instead of 64-bit mode.