SSIS project error: could not retrieve the table information for the connection manager 'excel connection manager'

Question

I'm new to SSIS. For my practice, I want to transfer data from excel to SQL Server.

1. I have created a connection manager for MS SQL Server (by selecting Connection manager for OLE DB Connections)

2. Another connection manager for EXCEL file

3. I have added an excel source to the Data Flow.

4. Now, I'm trying to edit the Excel source so that I can view the data inside the Excel file which throws following error.

"could not retrieve the table information for the connection manager 'excel connection manager' Failed to connect to the source using the connection manager ..."

After searching a lot including in StackOverflow posts, I found several causes and fixes for this error.

1. SQL Data Tools is only available in 32bitversoin. So, install "Microsoft Access Database Engine 2010"

2. Change DataMigration Property page configurations. Change Run64BitRuntime to False.

3. Change Excel Connection manager options "Excel Version" to "Microsoft Excel 97-2003" and to other options as well.

4. Make sure that excel file that I am trying to pull data from is not open while doing all these.

5. Set DelayValidation property to true

Tried every option found on the Internet but nothing worked. Now, I just want to get this fixed no matter what. Any suggestions to fix this issue would be greatly appreciated.

Answer 1

I have MS Office 2019 but the version of Excel I believe is 2016. In order for me to use Excel as my destination in SSIS, I have to use Microsoft Excel 97-2003 when configuring the Excel Connection Manager. And it worked. But you have to set the Run64BitRuntime to False if your machine is 64-bit. You can access Run64BitRuntime by right-clicking on your project --> Properties -> Configuration Properties -> Debugging -> Debug Options.

Answer 2

Just install Microsoft Access Database 2010 Engine 32bit go with the same process it works for me, FYI i have VS2019, Excel 64 bit

Answer 3

Now, I have a Solution of this problem. I am using visual studio 2015, MS Excel 2016, OS Window 11 64bit and I didn't even change my DelayValidation property.

1. Install the Microsoft Access Database 2010 Engine 32bit.
2. Drag and Drop the Excel Component.
3. Double click on Component and choose the path with Extension of .xlsx.
4. Select the Excel version 2007-2010.

Finally I figured out, I hope you can understand.

Answer 4

Since visual studio is 32 bit install Microsoft Access Database Engine 2010 Redistributable 32 bit version to enable excel driver from below link

https://www.microsoft.com/en-us/download/details.aspx?id=13255

Answer 5

Simple workaround. Open up the Excel data source save as Excel 97-2003 Workbook and in the SSIS Excel connection manager specify the Excel version as Excel 97-2003.

Answer 6

I was using Excel version 2019 in my PC and in SSIS: Excel Source editor was not showing 2019 version to choose. Hence I selected 2016 as highest version.

This is the mistake I did.

I saved my excel file in MS EXCEL 97-2003 Worksheet in PC and selected same version i.e., MS EXCEL 97-2003 in EXCEL SOURCE EDITOR in Visual Studio.

Solution: we must choose exact version of Excel version which is used in our PC and select the same in excel source editor in visual studio.

Answer 7

For me, it worked by uninstalling Microsoft Access Database Engine 2010 64Bit and installing 32 bit. FYI, I have VS2019 32bit running on Win10 and Excel 64bit.

Answer 8

After researching everywhere finally i have found out temporary solution. Because i have try all the solution installing access drivers but still i am facing same issues.

For excel source, Before this step you need to change the setting. Save excel file as 2010 format.xlsx

Also set Project Configuration Properties for Debugging Run64BitRuntime = False

1. Drag and drop the excel source
2. Double click on the excel source and connect excel. Any way you will get an same error no table or view cannot load....
3. Click ok
4. Right click on excel source, click on show advanced edit.
5. In that click on component properties.
6. You can see openrowset. In that right side you need to enter you excel sheet name example: if in excel sheet1 then you need to enter sheet1$. I.e end with dollar symbol. And click ok.
7. Now you can do other works connecting to destination.

I am using visual studio 2017, sql server 2017, office 2016, and Microsoft access database 2010 engine 32bit. Os windows 10 64 bit.

This is temporary solution. Because many peoples are searching for this type of question. Finally I figured out and this solution is not available in any of the website.

Answer 9

After trying all of the below possible solutions as mentioned in the question, frustratingly I decided to uninstall SSDT and installed it again and it worked for me though still had to follow the first 2 recommendations below.

• SQL Data Tools is only available in 32bitversoin. So, install "Microsoft Access Database Engine 2010"

• Change DataMigration Property page configurations. Change Run64BitRuntime to False.

• Change Excel Connection manager options "Excel Version" to "Microsoft Excel 97-2003" and to other options as well.

• Make sure that excel file that I am trying to pull data from is not open while doing all these.

• Set DelayValidation property to true

Answer 10

If you have 32-bit version of Office, then please download 32-bit drivers, else download 64-bit drivers. It worked for me, I just installed this file, {32 bit} (https://www.microsoft.com/en-za/download/confirmation.aspx?id=13255)

Hope this helps you resolve the issue

Answer 11

Problem is Excel data source was Office 2007, a lower version than the Office 2010. In this case, separate drivers are required: 2007 Office System Driver: Data Connectivity Components.

Download and install this:

http://www.microsoft.com/download/en/confirmation.aspx?id=23734