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

查看:13
本文介绍了空 Excel 文件权限问题:SSIS Excel 目标通过 C:UsersDefault 缓冲大记录集的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

通过 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 系统管理员时,它仍然无法将任何行写入更大的 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:UsersDefaultAppDataLocalMicrosoftWindowsTemporary Internet Files.当有限的用户帐户在 C:UsersDefault 下获得访问和权限时,一切正常.

Using Process Monitor reveals that ISServerExec.exe is trying to save a buffer to C:UsersDefaultAppDataLocalMicrosoftWindowsTemporary Internet Files. When the limited user account is given access and permissions under C:UsersDefault, everything works.

这解释了为什么交互式登录用户或本地管理员帐户能够将数据流记录写入 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:UsersDefault 缓冲大记录集的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆