SSIS Excel 连接管理器无法连接到源

SSIS Excel Connection Manager failed to Connect to the Source(SSIS Excel 连接管理器无法连接到源)

问题描述

我有一台能够使用导入向导创建和运行 Excel 导入任务的服务器.我正在尝试使用我在该服务器上开发的 Visual Studio 2010 集成服务包来自动化该过程.

I have a server that is capable of creating and running an Excel Import task using the Import Wizard. I am trying to automate that process by using a visual Studio 2010 Integration Services package, that I am developing on that server.

尝试设计包时出现问题.我添加了一个 excel 连接并将其指向本地磁盘上的 Excel 文件(我已经使用导入向导成功导入的同一个文件).当我将 Excel 源添加到 DataFlow 并指定 Excel 连接时，当我转到 Excel 工作表名称下拉菜单时，我只看到无法加载表或视图"并收到以下错误.

The problem happens when trying to design the package. I have added an excel connection and pointed it at the Excel file on a local disk (the same file I have already successfully imported using the import wizard). When I add an Excel Source to the DataFlow and specify the excel connection, when I go to the Name Of the Excel Sheet Drop down I just see "No tables or views can be loaded" and get the following error.

无法检索连接管理器的表信息.无法使用连接管理器连接到源..."

"Could not retrieve the table information for the connection manager. Failed to connect to the source using the connection manager ..."

我在任何地方都找不到这个错误记录，我不知道它为什么会失败.该目录已共享给经过身份验证的用户，并且该文件未在使用中.

I can't find this error logged anywhere and i don't know why it is failing. The directory is shared to Authenticated users and the file is not in use.

任何想法如何调试此错误?我知道在 64 位模式下运行它可能会出现问题，但这是否适用于开发?

Any ideas how to debug this error? I understand there can be issues running this in 64 bit mode, but does that apply to development?

我应该补充一点，它是一个 excel 2007 文件 .XLSX 并且连接设置为 Excel 2007.

I should add that it is an excel 2007 file .XLSX and the connection is set to Excel 2007.

2019-11-08 下面 GavB841 的回答看起来很有希望，如果有人尝试过并且有效，请告诉我.(我不再从事该领域的工作.)

2019-11-08 The answer by GavB841 below looks promising, if anyone tries it and it works please let me know. (I am no longer working in this area.)

推荐答案

似乎没有安装 32 位版本的 Excel.记住SSDT 是一个 32 位的 IDE.因此，当从 SSDT 访问数据时使用 32 位数据提供程序.在外面运行包时SSDT 它以 64 位模式运行(并非总是如此，但大多数情况下)并使用64 位数据提供程序.

It seems like the 32-bit version of Excel was not installed. Remember that SSDT is a 32-bit IDE. Therefore, when data is access from SSDT the 32-bit data providers are used. When running the package outside of SSDT it runs in 64-bit mode (not always, but mostly) and uses the 64-bit data providers.

永远记住，如果你想在 64 位运行你的包(您应该瞄准)您将需要 32 位数据提供程序(用于 SSDT 中的开发)以及 64 位数据提供者(用于在生产中执行包).

Always keep in mind that if you want to run your package in 64-bit (which you should aim for) you will need both the 32-bit data providers (for development in SSDT) as well as the 64-bit data providers (for executing the package in production).

我从以下位置下载了 32 位访问驱动程序:

I downloaded the 32-bit access drivers from:

• Microsoft Access 数据库引擎 2010 可再发行版.

安装后，我可以看到工作表

After installation, I could see the worksheets

<小时>

来源:

• 使用 SSIS 从 Excel 中提取数据

这篇关于SSIS Excel 连接管理器无法连接到源的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！