获取“外部表不是期望的格式".尝试在SSIS中导入Excel文件时出现错误 [英] Getting "External table is not in the expected format." error while trying to import an Excel File in SSIS

问题描述

我正在尝试通过SSIS将Excel文件(.xls)导入到SQL Server中的表中.但是SSIS似乎无法将文件识别为有效的Excel文件.我收到以下错误:

I am trying to import an Excel file (.xls) via SSIS to a table in SQL Server. But SSIS doesn't seem to recognize the file as a valid Excel file. I get the following errors:

错误1:

[Excel来源[86]]错误:SSIS
错误代码DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.
对连接管理器"Carga Base Original"的AcquireConnection方法调用失败，错误代码为0xC0202009.在此之前可能会发布错误消息，并详细说明为什么AcquireConnection方法调用失败的原因.

[Excel Source [86]] Error: SSIS
Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.
The AcquireConnection method call to the connection manager "Carga Base Original" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

错误2:

[SSIS.Pipeline]
错误:Excel Source验证失败，并返回了错误代码0xC020801C.

[SSIS.Pipeline]
Error: Excel Source failed validation and returned error code 0xC020801C.

错误3:

[连接管理器"Carga Base Original"]错误:SSIS错误代码DTS_E_OLEDBERROR.发生OLE DB错误.错误代码:0x80004005. OLE DB记录可用.
来源:"Microsoft Access数据库引擎"
结果:0x80004005
说明:外部表的格式不正确".

[Connection manager "Carga Base Original"] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available.
Source: "Microsoft Access Database Engine"
Hresult: 0x80004005
Description: "External table is not in the expected format".

我的连接管理器属性是一个Excel Source，其属性如下所示:

My connection manager properties is an Excel Source, its properties look like this:

我正在通过变量传递Excel文件路径. Excel文件看起来不错，并且未损坏.我试图放置一个固定的Excel路径，尝试将其放置在Connection String属性(固定路径和变量)中，但是这些都无济于事.

I am passing the Excel file path through a variable. The Excel file looks OK and is not corrupted. I tried to put a fix Excel path, try to put it in Connection String property (fix path and variable), but nothing of this helped.

有人可以帮助我吗?

推荐答案

主要错误是:

外部表的格式不正确

External table is not in the expected format

当Excel文件已损坏并且无法通过Access Database Engine(OLE DB提供程序)打开Excel文件时，即使您可以从Excel打开文件，也会发生这种情况.在类似的情况下，手动打开文件并将其另存为新文件可以解决问题.

This happens when the Excel file is corrupted and cannot be opened by Access Database Engine (OLE DB provider) even if you can open the file from Excel. In a similar case opening the file manually and saving it as a new file may do the trick.

这篇关于获取“外部表不是期望的格式".尝试在SSIS中导入Excel文件时出现错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！