Data driven UI Tests, fixing the data connection when executing on a remote agent

*Moved to: http://fluentbytes.com/data-driven-ui-tests-fixing-the-data-connection-when-executing-on-a-remote-agent/

This week I deployed a CodedUI solution with a customer where we created an extensive UI automation framework based on the page object pattern I described in one of my previous post.

One additional thing we added was the option to separate the test data entered in the UI from the actual tests. For this I leveraged the DataSource attribute that you can apply to any MS test based test, to bind to an Excel data sheet. In this sheet we created a sheet per logical data entity, so we could maintain the data scenarios separate from the C# code.

Here is an example of how to do this:

[TestMethod]
[DataSource("TestScenariosParticulier")]
public void TestScenario1()
{
    const string scenario = "Scenario1";
    // Load data from Excel
    var testcase = new ExcelDataTestcase(TestContext, scenario);
    
    // Create a new page object
    var polisManager = new PolisAanmaken(driver, testcase);
    
    string PolicyNummer= polisManager.GetTravelPolicy().PolicyNummer;
    
    Assert.IsTrue(int.TryParse(PolicyNummer),"Expect ploicy number is an Integer");
}

I won’t go into the details of loading the excel data sheet here, but the point is that the data source attribute points to a configfile that contains a pointer to the data source. The config file looks as follows:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <configSections>
        <section name="microsoft.visualstudio.testtools" type="Microsoft.VisualStudio.TestTools.UnitTesting.TestConfigurationSection, Microsoft.VisualStudio.QualityTools.UnitTestFramework, Version=10.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"/>
    </configSections>
    <connectionStrings>
        <add name="TestScenariosParticulierXlsxConnection" connectionString="Dsn=Excel Files;Driver={Microsoft Excel Driver (*.xlsx)};dbq=|DataDirectory|TestScenarios_particulier.xlsx;defaultdir=.;driverid=790;maxbuffersize=2048; pagetimeout=5;readonly=true;Extended Properties='HDR=NO'" providerName="System.Data.Odbc" />
    </connectionStrings>
    <microsoft.visualstudio.testtools>
        <dataSources>
            <add name="TestScenariosParticulier" connectionString="TestScenariosParticulierXlsxConnection" dataTableName="Driver$" dataAccessMethod="Sequential"/>
        </dataSources>
    </microsoft.visualstudio.testtools>
</configuration>

In this config file you can see I use an excel sheet called TestScenarios_Particulier.xlsx.

Now this all worked great on the developer machine, but when I wanted to run the automated test using Microsoft Test Manager (MTM) it just did not work. In MTM you can configure a test case to be automated and then the test is executed using the provided Build definition (to find the actual automation code) and the configured the Test Controller and Test Agent. For this purpose we create a new fresh test machine that contains nothing more then the browsers we wanted to use for the tests (IE, Firefox and Chrome) and the Cross browser components for CodedUI.

When I executed the tests, I constantly received the following error:

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

Unfortunately the link provided in the error does not help much, since it points to a generic article on testing.

So after some digging around I found that the solution to this problem was to install the “2007 Office System Driver: Data Connectivity Components“

We used a new fresh machine containing Windows Server 2012 as the operating system. But apparently when you look at the ODBC configuration on this machine, default there are no drivers available for Excel. I first tried to fix this by installing Excel itself on the machine, but that did not solve the issue either. To be honest I am glad for that, since I did not like the fact that I would need to install Excel on the machine to use the sheets. Luckily the driver package for Office 2007 solves the issue that the ODBC driver for Excel could not be found.

So installing the driver package solved the error and now my tests work great, straight from MTM. I now can click RunWithOptions and then select my test machine to run the test. After a few seconds, you can then watch the browser start and the test go through the scenario and all is nicely reported back to MTM, including a video recoding of the test run. Now that is Nice!

So I hope my Endeavour in fixing the issue will help you out as well, when you try to build data driven CodedUI tests.

Enjoy!

Marcel

Follow my new blog on http://fluentbytes.com