MSTest data driven tests - Using Excel 2007 as a data source

Today I had to troubleshoot a problem that we were experiencing with data driven tests not running on the build server.

Basically, we had done a copy/paste from the web to wire-up a data source to point at a spreadsheet of data:

[DataSource(
    "System.Data.Odbc", 
    "Dsn=Excel Files;dbq=|DataDirectory|\\Data\\mydata.xlsx;defaultdir=\\Data;driverid=1046,maxbuffersize=2048;pagetimeout=5", 
    "MySheet$", 
    DataAccessMethod.Sequential)]

This is the stock standard example that you see for configuring an Excel data source around the web.  Other versions that you come across quite often, include using a Jet Driver provider or, reverting to one of the Visual Studio test tools providers (e.g. CSV or XML).

The issue that we faced was that, using the Odbc driver, tests were running fine on our developer machines (Windows 7) but were failing with the following message on our build server (Windows 2008 R2):

The unit test adapter failed to connect to the data source or to read the data. For more information on troubleshooting this error, see "Troubleshooting Data-Driven Unit Tests" (http://go.microsoft.com/fwlink/?LinkId=62412) in the MSDN Library.
Error details: ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

What I presume is occurring is that Windows 7 must include a whole lot of drivers that make backwards compatibility easier while Win2k8R2 only installs a minimal set.  And therefore the older Jet and OleDb drivers were not installed on the server.

The fix is to simply use the current OleDb provider for Office 2007 formatted documents.  The final solution for our data source looked like this:

[DataSource(
    "System.Data.OleDb",
    "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=mydoc.xlsx;Persist Security Info=False;Extended Properties='Excel 12.0 Xml;HDR=YES'",
    "MySheet$", 
    DataAccessMethod.Sequential)]
[DeploymentItem("\\Data\\mydoc.xlsx")]
[TestMethod]
public void MyTestMethod()
{
   ...
}