Problems connecting to Excel workbook from SSIS
A short blog explaining why you might have problems connecting to an Excel workbook from SSIS.

Posted by Andy Brown on 16 April 2013

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Can't connect to Excel workbook using SSIS Package

This short blog explains why you might get the messages:

  • No tables or views could be loaded; or
  • Could not retrieve the table information for the connection manager ...; or
  • Failed to connect to the source using the connection manager ...

The Scenario

You've created an Excel connection to a workbook (here we're using a workbook of X factor contestants - sorry, rest-of-world):

The Excel connection manager

The Excel connection manager is shown selected.

 

You then add an Excel data source to a data flow task:

Excel data source

The Excel data source, awaiting configuration.

 

You then double-click on the data source to configure it, and get this:

No tables or views could be loaded

The error message: No tables or views could be loaded.

This message then appears:

Could not connect to source

You have failed to connect to the source using the connection manager!

 

The Solution

A bit of an anti-climax after all that: you've got the Excel workbook open!  Just close it, and try everything again, and it should work:

Open workbook

It's those wretched X-Factor contestants - you've left the workbook open.

Just thought I'd share that frustration - and solution.  You might also like to see information on our SSIS courses and Excel courses.

This blog has 0 threads Add post