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.

Solution

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