Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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 ..."

image description here

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

image description here

image description here

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.

like image 573
LearnCodeFun Avatar asked Nov 19 '22 23:11

LearnCodeFun


1 Answers

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

like image 178
RAM Boyapati Avatar answered May 17 '23 13:05

RAM Boyapati