SSIS Excel目标为空 [英] SSIS Excel Destination is Empty
问题描述
我在通过SSIS将大型数据集(500k +)导出到Excel时遇到问题，其中输出文件最终导出了0行.在说我不应该将那么多的记录导出到Excel之前，请允许我说出我知道的信息，通常不会.会计部门不需要CSV，并且不愿意在Excel中打开CSV.
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.
使用Visual Studio 2012 SSDT，下面是其中涉及的组件.
Using Visual Studio 2012 SSDT, here are the components involved.
- 执行SQL任务->创建带有标题的空文件
- 数据流任务->
- OLE DB源-> SQL查询
- Excel目的地
当程序包正在运行时，您可以看到从源流向目的地的记录.程序包无错误地完成，但是当您打开文件时，它为空.头中唯一的东西.
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.
如果我选择前1000条记录并导出到Excel，它将按预期工作.
If I select the Top 1000 records and export to Excel, it works as intended.
我尝试过的一些事情:
- 导出到网络上的Excel
- 本地导出到Excel
- 在网络上和本地上都导出为CSV到Excel
- 使用具有"Excel 12.0"扩展属性的Office Access Database Engine 12.0导出到Ole DB目标.
- 尝试以其他用户身份运行
所有结果相同.
任何人都可以提供关于为什么会发生这种情况以及如何进行的任何见解吗?
Can anyone provide any insight into why this may be happening and how to proceed?
推荐答案
在SQL Server代理作业中运行ETL时，我们遇到了类似的行为.但是，可以在Visual Studio中对其进行调试.因此，我不知道此解决方案是否适用于您.
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.
原因是运行包的用户没有访问C:\ users \ Default的权限.
The reason was that the user, under which the package ran, did not have access to C:\users\Default.
我通过使用sysinternals进程监视器发现了这一点.
I found this out by using sysinternals process monitor.
我受到那篇文章的启发:空的Excel文件权限问题:SSIS Excel目标通过C:\ Users \ Default
I was inspired by that post: Empty Excel File permissions issue: SSIS Excel Destination buffers large record sets through C:\Users\Default
[我在博客中解释了对该错误的搜索: https://www.csopro.de/biblog/2018/04/ssis-fehlerbehebung-bei-excel-destination-schreibt-keine-zeilen/不幸的是它在德语]
[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]
这篇关于SSIS Excel目标为空的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
