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.
我有一个能够使用Import向导创建和运行Excel导入任务的服务器。我试图通过使用visual Studio 2010集成服务包(我正在该服务器上开发)来自动化这个过程。
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.
当尝试设计包时出现了问题。我添加了一个excel连接，并将它指向本地磁盘上的excel文件(使用import向导已成功导入的文件)。当我向DataFlow添加一个Excel源并指定Excel连接时，当我转到Excel表的名称下拉时，我只看到“无法加载任何表或视图”，并得到以下错误。
"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.
我在任何地方都找不到这个错误，我也不知道为什么它会失败。该目录被共享给经过身份验证的用户，并且该文件没有被使用。
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?
如何调试这个错误?我知道在64位模式下可能会有问题，但这适用于开发吗?
I should add that it is an excel 2007 file .XLSX and the connection is set to Excel 2007.
我应该添加它是一个excel 2007文件。xlsx，连接设置为excel 2007。
7 个解决方案
#1
27
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.
似乎没有安装32位版本的Excel。记住，SSDT是一个32位IDE。因此，当从SSDT访问数据时，将使用32位数据提供程序。当在SSDT之外运行包时，它以64位模式(并非总是如此，但主要如此)运行，并使用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).
请始终记住，如果您希望在64位(应该针对64位)运行包，那么您将需要32位数据提供程序(用于SSDT的开发)和64位数据提供程序(用于在生产环境中执行包)。
I downloaded the 32-bit access drivers from http://www.microsoft.com/en-za/download/details.aspx?id=13255. After installation, I could see the worksheets
我从http://www.microsoft.com/en-za/download/details.aspx?id=13255下载了32位访问驱动程序。安装完毕后，我可以看到工作表。
Source: https://mariussqlbi.wordpress.com/2013/09/06/extracting-data-from-excel-with-ssis/
来源:https://mariussqlbi.wordpress.com/2013/09/06/extracting-data-from-excel-with-ssis/
#2
19
The workaround is, I I save the excel file as excel 97-2003 then it works fine
解决方法是，我将excel文件保存为excel 97-2003然后它就可以正常工作了
#3
7
I also ran into this problem today, but found a different solution from using Excel 97-2003. According to Maderia, the problem is SSDT (SQL Server Data Tools) is a 32bit application and can only use 32bit providers; but you likely have the 64bit ACE OLE DB provider installed. You could play around with trying to install the 32bit provider, but you can't have both the 64 & 32 version installed at the same time. The solution Maderia suggested (and I found worked for me) was to set the DelayValidation = TRUE on the tasks where I'm importing/exporting the Excel 2007 file.
我今天也遇到了这个问题，但是找到了一个不同的解决方案，使用Excel 97-2003。根据Maderia的说法，问题是SSDT (SQL Server Data Tools)是一个32位的应用程序，只能使用32位的提供程序;但是您可能已经安装了64位ACE OLE DB提供程序。您可以尝试安装32位提供程序，但是不能同时安装64和32版本。Maderia建议(我发现对我来说可行)的解决方案是在导入/导出Excel 2007文件的任务上设置DelayValidation = TRUE。
#4
4
Simple workaround is to open the file and simply press save button in Excel (no need to change the format). once saved in excel it will start to work and you should be able to see its sheets in the DFT.
简单的解决方法是打开文件并在Excel中按下save按钮(不需要更改格式)。一旦保存在excel中，它将开始工作，您应该能够在DFT中看到它的表。
#5
3
Recommendations from this article "Extracting Data From Excel with SSIS" https://mariussqlbi.wordpress.com/2013/09/06/extracting-data-from-excel-with-ssis/ resolved the issue for me, too.
本文中的建议“使用SSIS从Excel中提取数据”https://mariussqlbi.wordpress.com/2013/09/06/extracting-data-from-excel-with-ssis/也为我解决了这个问题。
I downloaded MS Access Database Engine 2010 32 bit driver
from the link in that article.
我从本文中的链接下载了MS Access数据库引擎2010 32位驱动程序。
Also set Project Configuration Properties for Debugging Run64BitRuntime = False
还要为调试Run64BitRuntime = False设置项目配置属性
In SQL Server 2014 SSMS (Integration Service Catalogue -> SSISDB -> Environments -> Projects for all Packages in Validate checked box "32 bit Runtime".
在SQL Server 2014 SSMS(集成服务目录-> SSISDB ->环境->项目，用于验证复选框“32位运行时”中的所有包)中。
My SSIS packages are working now in both VS 2013 and SQL Server 2014 environments.
我的SSIS包现在可以在VS 2013和SQL Server 2014环境中工作。
#6
2
You need to use an older version of the data connectivity driver (https://www.microsoft.com/en-in/download/confirmation.aspx?id=23734) and select Excel version 2007-2010 in the connection manager configuration window. I assume the newest data connectivity driver for Office 2016 is corrupt
您需要使用旧版本的数据连接驱动程序(https://www.microsoft.com/en- in/download/confirm.aspx? id=23734)，并在connection manager配置窗口中选择Excel version 2007-2010。我认为Office 2016最新的数据连接驱动程序是腐败的
#7
0
My answer is very similar to the one from @biscoop, but I am going to elaborate a bit as is may apply to the question or to other people.
我的回答与@biscoop非常相似，但是我将会详细说明这个问题或其他人可能遇到的问题。
I had a .xls that was an extraction from one of our webapps. The Excel connection would not work (error message: "no tables or views could be loaded"). As a side note, when opening the file, there would be a warning stating that the file was from an online source and that the content needed activation.
我有一个。xls是从我们的一个网络应用程序中提取出来的。Excel连接无法工作(错误消息:“无法加载表或视图”)。作为补充说明，当打开文件时，会有一个警告，说明该文件来自在线源，并且内容需要激活。
I tried to save the same file as an .xlsx and it worked. I tried to save the same file with another name as an .xls and it worked too. So as a last test I only opened the source .xls file, clicking save and the connection worked.
我试图保存与.xlsx相同的文件，并且它成功了。我尝试用另一个名称作为.xls保存相同的文件，它也成功了。因此，作为最后一个测试，我只打开源.xls文件，单击save，连接就工作了。
Short answer: just try and see if opening the file and saving does the trick.
简短的回答:试着看看打开文件和保存是否有效果。
#1
27
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.
似乎没有安装32位版本的Excel。记住，SSDT是一个32位IDE。因此，当从SSDT访问数据时，将使用32位数据提供程序。当在SSDT之外运行包时，它以64位模式(并非总是如此，但主要如此)运行，并使用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).
请始终记住，如果您希望在64位(应该针对64位)运行包，那么您将需要32位数据提供程序(用于SSDT的开发)和64位数据提供程序(用于在生产环境中执行包)。
I downloaded the 32-bit access drivers from http://www.microsoft.com/en-za/download/details.aspx?id=13255. After installation, I could see the worksheets
我从http://www.microsoft.com/en-za/download/details.aspx?id=13255下载了32位访问驱动程序。安装完毕后，我可以看到工作表。
Source: https://mariussqlbi.wordpress.com/2013/09/06/extracting-data-from-excel-with-ssis/
来源:https://mariussqlbi.wordpress.com/2013/09/06/extracting-data-from-excel-with-ssis/
#2
19
The workaround is, I I save the excel file as excel 97-2003 then it works fine
解决方法是，我将excel文件保存为excel 97-2003然后它就可以正常工作了
#3
7
I also ran into this problem today, but found a different solution from using Excel 97-2003. According to Maderia, the problem is SSDT (SQL Server Data Tools) is a 32bit application and can only use 32bit providers; but you likely have the 64bit ACE OLE DB provider installed. You could play around with trying to install the 32bit provider, but you can't have both the 64 & 32 version installed at the same time. The solution Maderia suggested (and I found worked for me) was to set the DelayValidation = TRUE on the tasks where I'm importing/exporting the Excel 2007 file.
我今天也遇到了这个问题，但是找到了一个不同的解决方案，使用Excel 97-2003。根据Maderia的说法，问题是SSDT (SQL Server Data Tools)是一个32位的应用程序，只能使用32位的提供程序;但是您可能已经安装了64位ACE OLE DB提供程序。您可以尝试安装32位提供程序，但是不能同时安装64和32版本。Maderia建议(我发现对我来说可行)的解决方案是在导入/导出Excel 2007文件的任务上设置DelayValidation = TRUE。
#4
4
Simple workaround is to open the file and simply press save button in Excel (no need to change the format). once saved in excel it will start to work and you should be able to see its sheets in the DFT.
简单的解决方法是打开文件并在Excel中按下save按钮(不需要更改格式)。一旦保存在excel中，它将开始工作，您应该能够在DFT中看到它的表。
#5
3
Recommendations from this article "Extracting Data From Excel with SSIS" https://mariussqlbi.wordpress.com/2013/09/06/extracting-data-from-excel-with-ssis/ resolved the issue for me, too.
本文中的建议“使用SSIS从Excel中提取数据”https://mariussqlbi.wordpress.com/2013/09/06/extracting-data-from-excel-with-ssis/也为我解决了这个问题。
I downloaded MS Access Database Engine 2010 32 bit driver
from the link in that article.
我从本文中的链接下载了MS Access数据库引擎2010 32位驱动程序。
Also set Project Configuration Properties for Debugging Run64BitRuntime = False
还要为调试Run64BitRuntime = False设置项目配置属性
In SQL Server 2014 SSMS (Integration Service Catalogue -> SSISDB -> Environments -> Projects for all Packages in Validate checked box "32 bit Runtime".
在SQL Server 2014 SSMS(集成服务目录-> SSISDB ->环境->项目，用于验证复选框“32位运行时”中的所有包)中。
My SSIS packages are working now in both VS 2013 and SQL Server 2014 environments.
我的SSIS包现在可以在VS 2013和SQL Server 2014环境中工作。
#6
2
You need to use an older version of the data connectivity driver (https://www.microsoft.com/en-in/download/confirmation.aspx?id=23734) and select Excel version 2007-2010 in the connection manager configuration window. I assume the newest data connectivity driver for Office 2016 is corrupt
您需要使用旧版本的数据连接驱动程序(https://www.microsoft.com/en- in/download/confirm.aspx? id=23734)，并在connection manager配置窗口中选择Excel version 2007-2010。我认为Office 2016最新的数据连接驱动程序是腐败的
#7
0
My answer is very similar to the one from @biscoop, but I am going to elaborate a bit as is may apply to the question or to other people.
我的回答与@biscoop非常相似，但是我将会详细说明这个问题或其他人可能遇到的问题。
I had a .xls that was an extraction from one of our webapps. The Excel connection would not work (error message: "no tables or views could be loaded"). As a side note, when opening the file, there would be a warning stating that the file was from an online source and that the content needed activation.
我有一个。xls是从我们的一个网络应用程序中提取出来的。Excel连接无法工作(错误消息:“无法加载表或视图”)。作为补充说明，当打开文件时，会有一个警告，说明该文件来自在线源，并且内容需要激活。
I tried to save the same file as an .xlsx and it worked. I tried to save the same file with another name as an .xls and it worked too. So as a last test I only opened the source .xls file, clicking save and the connection worked.
我试图保存与.xlsx相同的文件，并且它成功了。我尝试用另一个名称作为.xls保存相同的文件，它也成功了。因此，作为最后一个测试，我只打开源.xls文件，单击save，连接就工作了。
Short answer: just try and see if opening the file and saving does the trick.
简短的回答:试着看看打开文件和保存是否有效果。