CSS SQL Server Engineers

This is the official team Web Log for Microsoft Customer Service and Support (CSS) SQL Support. Posts are provided by the CSS SQL Escalation Services

PowerPivot Table Import Wizard cannot find provider

PowerPivot Table Import Wizard cannot find provider

  • Comments 3

The data source provider list in PowerPivot can often be a source of confusion for users since they equate the fact that a provider appears in the list as the provider being installed and available. Unfortunately, the list of providers is actually a static list of supported data sources for PowerPivot, so the user is still required to install the desired provider to successfully import data into PowerPivot. Thus, the most common fix for a "provider is not installed" error in the import wizard is to ensure you have the proper data provider installed and that the installed provider matches the platform architecture (32-bit or 64-bit) of PowerPivot and Excel.

If you are certain that the selected provider is installed on your client machine and are able to import data directly into Excel using the desired provider via the Data tab, then you may be encountering another issue which was recently discovered.

In this new scenario data import in PowerPivot will fail for any provider selected. The exact error seen varies depending on the provider selected but examples include:

Text File:  "Details: Failed to connect to the server. Reason: Provider information is missing from the connection string"

Excel:  "Cannot connect to the data source because the Excel provider is not installed."

SQL Server: "Cannot connect to the data source because the SQLServer provider is not installed."

 

The problem is actually due to a problem with the .NET machine configuration. PowerPivot attempts to instantiate providers by using the .NET DbProviderFactory class. If an error is encountered while instantiating the DbProviderFactory class, the error for the DbProviderFactory is not returned, instead the message returned is that the selected provider is not installed. If you are encountering this scenario it is very likely that there is a problem instantiating the .NET DBProviderFactory class.

The DbProviderFactory class configuration is read from the Machine.Config.xml file, which depending on whether you are running the 32-bit or 64-bit version of Excel and PowerPivot is located at:

c:\Windows\Microsoft.NET\Framework\v4.0.30319\Config

or

c:\Windows\Microsoft.NET\Frameworkx64\v4.0.30319\Config

Checking the Machine.Config.xml file you will find the <DBProviderFactories> element under <system.data>.  The <DBProviderFactories> element should only appear once, but problematic machines may have more than one XML tag for DbProviderFactories.

Example of bad element list:
<
system.data> 
     <DbProviderFactories>

        <add name="Microsoft SQL Server Compact Data Provider"invariant="System.Data.SqlServerCe.3.5"description=".NET Framework Data Provider for Microsoft SQL Server Compact" type="System.Data.SqlServerCe.SqlCeProviderFactory, System.Data.SqlServerCe, Version=3.5.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"/>

 </DbProviderFactories>
<DbProviderFactories/> 
</system.data>

NOTE: The begin and end tag around the add for the SQLServerCE provider, followed by the empty element tag.

Correct Example:

 <system.data> 
     <DbProviderFactories>

        <add name="Microsoft SQL Server Compact Data Provider"invariant="System.Data.SqlServerCe.3.5"description=".NET Framework Data Provider for Microsoft SQL Server Compact" type="System.Data.SqlServerCe.SqlCeProviderFactory, System.Data.SqlServerCe, Version=3.5.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"/>

 </DbProviderFactories>
</system.data>

NOTE: The add element(s) between the open <DbProviderFactories> and close </DbProviderFactories> tags will vary depending on what providers are installed on your machine.

If you find that you have something similar to the bad example above, please use the following steps to resolve the issue:

  1. Make a backup copy of existing machine.config.xml file in the event you need to restore it for any reason.
  2. Open the machine.config.xml file in notepad or another editor of your choice.
  3. Delete the empty element tag <DbProviderFactories/> from the file.
  4. Save the updated file.
  5. Retry the import from PowerPivot 

 

Wayne Robertson - Sr. Escalation Engineer

Leave a Comment
  • Please add 1 and 3 and type the answer here:
  • Post
  • Thank you!!! this worked for me on windows 7 excel 2010 power pivot v11.

  • This worked like a charm! Thanks a ton!

  • I am using Excel 2010 on Windows 8.1. There is no machine.config.xml file - only a machine.config file - I have extensions viewing turned on. Not sure if this is the same file but on Windows 8.1 but would like to get this problem solved.

Page 1 of 1 (3 items)