SSIS导出到Excel [英] SSIS export to excel

问题描述

使用SSMS，我创建了一个将查询导出到Excel并将其保存为DTSX包的方法.将此DTSX包导入到BIDS项目中，使其每小时运行一次，并导出带有时间戳的excel文件，并使用表达式来动态命名.当我从BIDS运行时，程序包成功执行并成功创建了excel文件.我启用了DelayedValidation标志. 我可以双击DTSX文件，运行该程序包，它会毫无问题地创建excel文件.当我配置为SQL作业时，它失败并显示以下错误

Using SSMS I created an export a query to Excel and saved it as a DTSX package. Imported this DTSX package into a BIDS project to make it run hourly and export a excel file with timestamp appended to it, dynamic name using expressions. When I run from BIDS, package executes successfully and creates the excel file without a problem. I have enabled DelayedValidation flag. I can double click on the DTSX file, run the package and it creates the excel file without a problem. When I configure as a SQL Job, it fails with the below error

非常感谢任何指导

Microsoft (R) SQL Server Execute Package Utility  Version 10.50.2500.0 for 32-bit  Copyright (C) Microsoft Corporation 2010. All rights reserved.    
    Started:  2:53:03 PM  
    Error: 2012-09-21 14:53:05.54     
    Code: 0xC0202009     
    Source: Export MyExportQuery to Excel Destination - Query [73]     
    Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E37.  End Error  
    Error: 2012-09-21 14:53:05.54     
    Code: 0xC02020E8     
    Source: Export MyExportQuery to Excel Destination - Query [73]     
    Description: Opening a rowset for "Query" failed. Check that the object exists in the database.  End Error  
    Error: 2012-09-21 14:53:05.54     
    Code: 0xC004701A     
    Source: Export MyExportQuery to Excel SSIS.Pipeline     
    Description: component "Destination - Query" (73) failed the pre-execute phase and returned error code 0xC02020E8.  End Error  
    DTExec: The package execution returned DTSER_FAILURE (1).  
    Started:  2:53:03 PM  
    Finished: 2:53:05 PM  
    Elapsed:  2.282 seconds.  The package execution failed.  The step failed.

SSIS配置为以32位模式运行

SSIS is configured to run in 32 bit mode

推荐答案

大多数问题是由于以下情况引起的:

Most of the problems arise due to these situations:

• 数据源连接或文件访问问题(运行sql代理的用户对数据库或目标文件没有正确的权限时).

• Data source connection or File access issue (when the user running the sql agent doesn't have the right permissions to the db or to the destination file).

包装保护级别(pwd是敏感数据，有时会根据保护级别进行复制).

Package protection level (pwd are sensitive data, and sometimes are not copied depending on the protection level).

64位问题(这是XL导出中的常见问题.由于没有用于64位系统的Jet驱动程序，因此在64位OS上运行时，您需要在sql作业中使用32位DTEXEC或设置使用32位运行时"选项，它在创建SSIS作业步骤时位于执行选项"选项卡上，请查看下面链接上的图像以查看它.)

64bit issue (this a common one on XL exports. Since there's no Jet Driver for 64bit systems, when running on a 64bit OS you need to either use the 32bit DTEXEC in your sql job or set the "Use 32bit runtime" option, it's on the "Execution options" tab when you're creating a SSIS job step, check the image on link below for seeing it).

32位运行时选项映像

有关此主题的信息，请查看本文，其中包含有关sis包在BIDS上而不在SQL作业上运行的情况的详细信息.

For information on this topic, have a look at this article, it has detailed information on situations when ssis packages run on BIDS but not on SQL jobs..

如何进行故障排除SSIS包在SQL Agent作业中执行失败?

希望这对您有帮助.

亲切的问候，

这篇关于SSIS导出到Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！