空的Excel文件权限问题：SSIS Excel目标通过C：\Users\Default缓冲大型记录集 [英] Empty Excel File permissions issue: SSIS Excel Destination buffers large record sets through C:\Users\Default

问题描述

通过DTEXEC.EXE从Web应用程序调用SSIS包。程序包使用文件系统任务来制作要用作Excel目标文件的Excel模板文件的副本。然后，数据流任务将记录从OLE DB源发送到Excel目标。然后将Excel目标文件流式传输回Web应用程序用户的浏览器。

An SSIS package is invoked via DTEXEC.EXE from a web application. The package uses a File System Task to make a copy of an Excel template file to be used as the Excel destination file. A Data Flow Task then sends records from an OLE DB Source into the Excel Destination. The Excel destination file is then streamed back to the web application user's browser.

当Web应用程序池标识为本地管理员帐户时，此方法很好用。当Web应用程序池标识是受限用户并且恰好也是交互式登录时，它也可以工作。

This works fine when the web application pool identity is a local admin account. It also works when the web application pool identity is a limited user who happens to be interactively logged in as well.

当Web应用程序池标识是受限用户时，未同时登录，遇到问题。小型Excel文件（几百行）仍然可以正确返回。没问题。大型Excel文件（几万行）将返回为空。流文件中没有返回到用户浏览器的行。写入服务器的目标文件也为空。

When the web application pool identity is a limited user who is NOT concurrently logged in, a problem is encountered. Small Excel files (a few hundred rows) are still returned correctly. No problem there. Large Excel files (a few ten thousand rows) are returned empty. There are no rows in the streamed file that is returned to the user's browser. The destination file that is written on the server is also empty.

SSIS包没有看到错误，并报告数万条记录通过数据流传递。这不是SQL Server方面的权限问题。当为用户帐户赋予SQL Server sysadmin权限时，它仍然无法将任何行写入较大的Excel目标文件。

The SSIS package does not see an error, and reports that tens of thousands of records passed through the Data Flow. This is not a permissions issue on the SQL Server side. When the user account is given SQL Server sysadmin, it still fails to write any rows to a larger Excel destination file.

这看起来像Windows上的权限问题，也许涉及到将缓冲区写入磁盘，这就是为什么仅处理几百行时不会发生此问题的原因。

This looks like a permissions problem on the Windows side, perhaps involving a buffer being written to disk, which is why the problem does not occur when only a few hundred rows are processed.

希望避免磁盘缓冲通过增加 DefaultBufferMaxRows（10,000行）和DefaultBufferSize（10 MB）的数据流任务设置。但是，不幸的是，这是行不通的。

It was hoped that disk buffering could be avoided by increasing the Data Flow Task settings for DefaultBufferMaxRows (10,000 rows) and DefaultBufferSize (10 MB). However, unfortunately this does not work.

这是一个已解决的问题，旨在帮助任何可能遇到类似问题的人。

This is a solved problem that is posted to assist anyone who may encounter a similar problem.

推荐答案

使用进程监视器显示ISServerExec.exe试图将缓冲区保存到C：\Users\Default\AppData\Local\Microsoft\Windows\Temporary Internet Files。当在C：\Users\Default下授予受限用户帐户访问权限时，一切正常。

Using Process Monitor reveals that ISServerExec.exe is trying to save a buffer to C:\Users\Default\AppData\Local\Microsoft\Windows\Temporary Internet Files. When the limited user account is given access and permissions under C:\Users\Default, everything works.

这说明了为什么交互式登录的用户或本地用户admin帐户能够将数据流记录写入Excel文件，但是常规的批量登录却不能这样做。这是SSIS Excel目标的不良行为。

This explains why an interactively logged-in user or a local admin account was able to write Data Flow records to the Excel file, but a regular batch login was not able to do so. This is an undesirable behavior of the SSIS Excel Destination.

这篇关于空的Excel文件权限问题：SSIS Excel目标通过C：\Users\Default缓冲大型记录集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！