WiseOwl Training - Established 1992 Wise Owl Training

Established May 1992
30 years in business
Wise Owl Training
30 years in business
See 520 reviews for our classroom and online training
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