使用SQL AGENT JOB调度 SSIS包报错

jjx5373 2018-06-28 03:07:17

环境：

winServer2012 + SQL SERVER 2012

包内容：

将EXCEL文件（.XLSX）导入数据库表中。

1.IDE里运行加载成功。

2.SQL AGENT JOB中，部分成功，部分失败。导多个文件的同一个包中，也有部分文件成功，部分文件失败。

3.SQL AGENT JOB中通过本地帐号创建凭证来运行JOB，全部成功。

问题：1.不用本地账号的凭证能否让SQL AGENT JOB运行成功？怎么做？

2.如果只是权限问题，为什么会有包能够运行成功？

3.如果说文件有问题，为什么IDE里可以运行成功？

4.2007 Office System Driver: Data Connectivity Components 与Microsoft Access Database Engine 2010 Redistributable 对SSIS读取EXCEL（.xlsx）文件有什么差异吗？

2007 Office System Driver: Data Connectivity Components

下载地址：https://www.microsoft.com/en-us/download/details.aspx?id=23734

Microsoft Access Database Engine 2010 Redistributable

下载地址：https://www.microsoft.com/en-us/download/details.aspx?id=13255

...全文

132 点赞收藏 6

写回复

6 条回复

切换为时间正序

当前发帖距今超过3年，不再开放新的回复

发表回复

jjx5373 2018-06-29

引用 4 楼 yenange 的回复:

错误信息贴一下

Started: 11:30:57 AM Error: 2018-06-29 11:30:58.89 Code: 0xC0202009 Source: Connection manager "SourceConnectionExcel" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft Office Access Database Engine" Hresult: 0x80004005 Description: "External table is not in the expected format.". End Error Error: 2018-06-29 11:30:58.90 Code: 0xC020801C Source: Data Flow Task 1 Source - 'City$' [69] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "SourceConnectionExcel" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. End Error Error: 2018-06-29 11:30:58.90 Code: 0xC0047017 Source: Data Flow Task 1 SSIS.Pipeline Description: Source - 'City$' failed validation and returned error code 0xC020801C. End Error Error: 2018-06-29 11:30:58.90 Code: 0xC004700C Source: Data Flow Task 1 SSIS.Pipeline Description: One or more component failed validation. End Error Error: 2018-06-29 11:30:58.90 Code: 0xC0024107 Source: Data Flow Task 1 Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 11:30:57 AM Finished: 11:30:58 AM Elapsed: 1.56 seconds. The package execution failed. The step failed. This e-mail or any attachments may contain confidential or privileged information. Unless you are the intended recipient, you may not disclose, copy or use any information herein. If you have received this e-mail in error, please notify the sender immediately by reply and delete the e-mail from your system.

回复赞

jjx5373 2018-06-29

应该是数据文件用2007的组件读取不了，需要用2010的组件。但是，看了微软的安装说明，好像也没有提到有什么不同。