SSIS project error: Failed to get table information for connection manager "connection manager"
I am new to SSIS. For my practice, I want to transfer data from excel to SQL Server.
-
I created a Connection Manager for MS SQL Server (by selecting Connection Manager for OLE DB connections)
-
Another connection manager for the EXCEL file
-
I have added an Excel source to the dataflow.
-
Now I am trying to edit the Excel source so that I can view the data in the Excel file that is causing the following error.
"could not get table information for connection manager with connection manager" Could not connect to source using connection manager ... "
After searching a lot, including on StackOverflow posts, I found several reasons and fixes for this error.
-
SQL Data Tools are only available in 32-bit version. So, install "Microsoft Access Database Engine 2010"
-
Modify the properties of the DataMigration property page. Change Run64BitRuntime to False.
-
Change the Excel Connection Manager settings from Excel Version to Microsoft Excel 97-2003 and other settings.
-
Make sure the excel file I am trying to extract data does not open while doing all of these steps.
-
Set the DelayValidation property to true
Tried all the options found on the internet but nothing worked. Now I just want this to be fixed no matter what. Any suggestions to fix this issue would be greatly appreciated.
source to share
The problem is that the Excel data source was Office 2007, an earlier version than 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
source to share
If you have 32-bit Office installed, download 32-bit drivers, otherwise 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 solve the problem
source to share
After trying all the possible solutions listed below mentioned in this question, I was disappointed to uninstall SSDT and install it again, and it helped me, although I still had to follow the first two recommendations below.
SQL Data Tools are only available at 32bitversoin. So, install "Microsoft Access Database Engine 2010"
Modify the configurations of the DataMigration property page. Change Run64BitRuntime to False.
Change the Excel Connection Manager settings from Excel Version to Microsoft Excel 97-2003 and other settings.
Make sure the Excel file I'm trying to extract data from is not open during all of these steps.
Set the DelayValidation property to true
source to share
After researching everywhere I finally found a workaround. Because I've tried all solutions installing access drivers but still face the same problems.
For Excel source, Before this step, you need to change the settings. Save Excel file in 2010.xlsx format
Also set project config properties for debugging Run64BitRuntime = False
- Drag source Excel
- Double click the Excel source and connect to Excel. Either way you get the same error the table or view cannot load ....
- Click OK
- Right click on Excel source, click Show Advanced Editing.
- In this click on the properties of the component.
- You can see openrowset . On this right side, you need to enter your example of an Excel sheet name: if in Excel sheet1, then you need to enter sheet1 $. End with a dollar symbol. And click OK.
- You can now do other jobs by connecting to your destination.
I am using Visual Studio 2017, SQL Server 2017, Office 2016 and Microsoft Access database 2010 32bit engine. Windows 10 64 bit.
This is a temporary solution. Because many people are looking for this type of question. Finally, I figured it out and this solution is not on any site.
source to share